|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I am having a lot of trouble importing data into Access 2003. I am sick of
the invalid argument message to the point where I took a table, exported the
data to a csv file, deleted the data in the table, removed the indexes and
any primary keys, saved the table design, and I'm trying to import the data
that just came out of the database in the first place.
My database is small, about 3 meg.
My trouble shooting so far is to cut the csv down to one line of data. I
have removed the headings, so there is just data.
Now should I start removing all non text columns one at a time until I can
import at least one of them?
I am researching this all over the place and a lot of web sites say try this
or try that or the database is too big. What I would like to know is: what
is the root cause of this error message? I would like to get on with the
show, but I am stuck with this problem.
Can anyone help? Do I really have to write my own import program?
I am also trying to avoid the "Paste Append" trick, because loading the data
into Excel crops off leading zeros and changes some text columns into numeric
columns.
|
|
Have you created an Import Spec for the file you are trying to import?
What method are you using to do the import?
"Rob G" wrote:
[Quoted Text] > I am having a lot of trouble importing data into Access 2003. I am sick of
> the invalid argument message to the point where I took a table, exported the
> data to a csv file, deleted the data in the table, removed the indexes and
> any primary keys, saved the table design, and I'm trying to import the data
> that just came out of the database in the first place.
>
> My database is small, about 3 meg.
>
> My trouble shooting so far is to cut the csv down to one line of data. I
> have removed the headings, so there is just data.
>
> Now should I start removing all non text columns one at a time until I can
> import at least one of them?
>
> I am researching this all over the place and a lot of web sites say try this
> or try that or the database is too big. What I would like to know is: what
> is the root cause of this error message? I would like to get on with the
> show, but I am stuck with this problem.
>
> Can anyone help? Do I really have to write my own import program?
>
> I am also trying to avoid the "Paste Append" trick, because loading the data
> into Excel crops off leading zeros and changes some text columns into numeric
> columns.
>
>
|
|
I have not created an import spec.
I am using the Import Wizard.
"Klatuu" wrote:
[Quoted Text] > Have you created an Import Spec for the file you are trying to import?
> What method are you using to do the import?
>
> "Rob G" wrote:
>
> > I am having a lot of trouble importing data into Access 2003. I am sick of
> > the invalid argument message to the point where I took a table, exported the
> > data to a csv file, deleted the data in the table, removed the indexes and
> > any primary keys, saved the table design, and I'm trying to import the data
> > that just came out of the database in the first place.
> >
> > My database is small, about 3 meg.
> >
> > My trouble shooting so far is to cut the csv down to one line of data. I
> > have removed the headings, so there is just data.
> >
> > Now should I start removing all non text columns one at a time until I can
> > import at least one of them?
> >
> > I am researching this all over the place and a lot of web sites say try this
> > or try that or the database is too big. What I would like to know is: what
> > is the root cause of this error message? I would like to get on with the
> > show, but I am stuck with this problem.
> >
> > Can anyone help? Do I really have to write my own import program?
> >
> > I am also trying to avoid the "Paste Append" trick, because loading the data
> > into Excel crops off leading zeros and changes some text columns into numeric
> > columns.
> >
> >
|
|
I would suggest you try creating an Import Spec for the files. To do that,
when you get the wizard dialog, click on Advanced. It will allow you to
define field names, data types, and other import criteria. You can then
click Save As and give the spec a name so you can reuse it each time you need
to do the import.
"Rob G" wrote:
[Quoted Text] > I have not created an import spec.
> I am using the Import Wizard.
>
> "Klatuu" wrote:
>
> > Have you created an Import Spec for the file you are trying to import?
> > What method are you using to do the import?
> >
> > "Rob G" wrote:
> >
> > > I am having a lot of trouble importing data into Access 2003. I am sick of
> > > the invalid argument message to the point where I took a table, exported the
> > > data to a csv file, deleted the data in the table, removed the indexes and
> > > any primary keys, saved the table design, and I'm trying to import the data
> > > that just came out of the database in the first place.
> > >
> > > My database is small, about 3 meg.
> > >
> > > My trouble shooting so far is to cut the csv down to one line of data. I
> > > have removed the headings, so there is just data.
> > >
> > > Now should I start removing all non text columns one at a time until I can
> > > import at least one of them?
> > >
> > > I am researching this all over the place and a lot of web sites say try this
> > > or try that or the database is too big. What I would like to know is: what
> > > is the root cause of this error message? I would like to get on with the
> > > show, but I am stuck with this problem.
> > >
> > > Can anyone help? Do I really have to write my own import program?
> > >
> > > I am also trying to avoid the "Paste Append" trick, because loading the data
> > > into Excel crops off leading zeros and changes some text columns into numeric
> > > columns.
> > >
> > >
|
|
I have tried to create an import spec and failed. So here is what I did...
I exported a table, creating a .csv file and creating an export file spec.
I took the resulting file and removed all but one row of data. I then did an
import using the import wizard and the export file spec (you have to review
all the data types and adjust as needed. I had two columns that were left
blank and I made them doubles) and save that spec again as that tables'
import spec. Finally I finished importing the one row of data as my test.
Now it feels like we are getting somewhere. I opened up my .csv file and
took out the first 20 rows for a second test import. That worked too.
Finally I took the whole .csv file and tried an import. I got several errors
but no "Invalid argument" errors and the bad data rows went into a
...._ImportErrors table. This is GOOD!
So what I can conclude from this is:
The "Invalid argument" message has little to do with bad data and has more
to do with the fact that the import process cannot decide how to apply your
data to the table to which you are importing. This is why it seems you must
create an EXPORT FILE SPEC - which gets your column names correct, then you
must test this Export Spec as an IMPORT SPEC with sample data. If it doesn't
import still, then open the IMPORT SPEC and check the column data types for
accuracy or blank types. Then do your full import.
(As Jim Cramer says...) "THE BOTTOM LINE!" The KB help for "Invalid
argument" is not helpful at all. Thanks to Klatuu for stearing me in the
right direction. I hope this is useful to someone other than myself who may
be in a similar situation.
Rob.
"Klatuu" wrote:
[Quoted Text] > I would suggest you try creating an Import Spec for the files. To do that,
> when you get the wizard dialog, click on Advanced. It will allow you to
> define field names, data types, and other import criteria. You can then
> click Save As and give the spec a name so you can reuse it each time you need
> to do the import.
>
> "Rob G" wrote:
>
> > I have not created an import spec.
> > I am using the Import Wizard.
> >
> > "Klatuu" wrote:
> >
> > > Have you created an Import Spec for the file you are trying to import?
> > > What method are you using to do the import?
> > >
> > > "Rob G" wrote:
> > >
> > > > I am having a lot of trouble importing data into Access 2003. I am sick of
> > > > the invalid argument message to the point where I took a table, exported the
> > > > data to a csv file, deleted the data in the table, removed the indexes and
> > > > any primary keys, saved the table design, and I'm trying to import the data
> > > > that just came out of the database in the first place.
> > > >
> > > > My database is small, about 3 meg.
> > > >
> > > > My trouble shooting so far is to cut the csv down to one line of data. I
> > > > have removed the headings, so there is just data.
> > > >
> > > > Now should I start removing all non text columns one at a time until I can
> > > > import at least one of them?
> > > >
> > > > I am researching this all over the place and a lot of web sites say try this
> > > > or try that or the database is too big. What I would like to know is: what
> > > > is the root cause of this error message? I would like to get on with the
> > > > show, but I am stuck with this problem.
> > > >
> > > > Can anyone help? Do I really have to write my own import program?
> > > >
> > > > I am also trying to avoid the "Paste Append" trick, because loading the data
> > > > into Excel crops off leading zeros and changes some text columns into numeric
> > > > columns.
> > > >
> > > >
|
|
|