Problem importing CSV file into Table

[Lateral]

Hi Guys

My Access 2007 application uses DoCmd.TransferText option to import numerous CSV files into various tables and has been working well for years.

The want to change one of these Imports as there is an additional field in the CSV file that needs to be imported into the table. It is called "AvailOrder" and is a Yes/No field. Please note that there is also another Yes/No field already in the CSV file called "Avail".

I have added the new field to the "Web_Parts" table, its called "AvailOrder"

The header record of the CSV file also contains the new field as per the following example:

Active,AvailOrder,Web_Category,DateAdd,Web_Product_ID,ImageURL,PartURL,PartName,UnitPrice,Ref,Wholesale,SOH,Manufacturer
"0","1","Home > Brake Parts > Discs/Rotors & Hubs","2014-09-14 16:06:20","342","https://rodpenroseracing.com.au/img/p/6/7/2/672.jpg","https://rodpenroseracing.com.au/dis...-and-short-swing-axle-direct-replacement.html","4 x 130 Lug Rotor - Rear - IRS and short Swing axle - direct replacement","116.50","RPR-10342","0.00","9997","EMPI"
"0","1","Home > Brake Parts > Discs/Rotors & Hubs","2014-09-25 06:43:22","496","https://rodpenroseracing.com.au/img/p/9/3/5/935.jpg","https://rodpenroseracing.com.au/dis...y-custom-stud-pattern-direct-replacement.html","Blank Rotor - Rear - for any custom stud pattern - direct replacement","119.00","RPR-10496","0.00","9999","EMPI"

DoCmd.TransferText acImportDelim, "ImportWebParts", "Web_Parts", "C:\testmiport.csv", True, ""

Ok, now that's the background.

The problem I have is that if I use the "External Data" option on the ribbon to do the import WITHOUT a specification, it imports all of the records correctly.

When I use a specification that includes the new field, I get the following error:

I have spent hours and hours trying to figure out what is wrong to no avail.

Here is the table specs for Web_Parts:

Any help is much appreciated.

Cheers
Greg

 

[The_Doc_Man]

Not that it should care, but try making the 'true' field -1 instead of +1.

 

[Lateral]

Thanks doc man. I’ll try this tomorrow and let you know.

cheers
Greg

 

[June7]

Or don't use yes/no field. Review http://allenbrowne.com/NoYesNo.html

 

[arnelgp]

The problem I have is that if I use the "External Data" option on the ribbon to do the import WITHOUT a specification, it imports all of the records correctly.

then don't use any import/export specification for your import:

DoCmd.TransferText acImportDelim, , "Web_Parts", "C:\testmiport.csv", True, ""

 

[Lateral]

then don't use any import/export specification for your import:

DoCmd.TransferText acImportDelim, , "Web_Parts", "C:\testmiport.csv", True, ""

I have tried "DoCmd.TransferText acImportDelim, , "Web_Parts", "C:\testmiport.csv", True, "" this and get the following error:

If I don't use a Specification, how does it know what the Text Qualifier is?

 

[Lateral]

Not that it should care, but try making the 'true' field -1 instead of +1.

Hi Docman,

I changed it to "-1" but still get the error message when using a Specification but not when I don't use a Specfication.

This is driving me nuts................

 

[The_Doc_Man]

OK, if that doesn't work then the question is what it really wants for that field when importing. If you can open the input specification you can see what data type is being assigned for the two Y/N fields. I suspect that the problem occurs because the numeric inputs aren't what was expected. Which means I can't swear to you that it isn't the "0" that is tripping you just as much as the "1" (or "-1").

When you import WITHOUT a specification, there is an implied data type conversion between what you submitted in the CSV and what you stored in the table. In that case, the 0 and 1 or -1 for a Boolean field will not cause heartache. They will convert to what you want without problems. On the other hand, if you have a specific data type other than integer for those fields, you might have to play with using True and False or Yes and No for the two fields in question.

 

[ebs17]

DoCmd.TransferText acLinkDelim, "ImportWebParts", "Web_Parts", "C:\testmiport.csv", True, ""

Link the CSV. This allows you to see what content is actually being delivered. Using a recordset on it, you can check the resulting data types of the fields (Field.Type). You can use an append query to test how the data is accepted by the target table and perhaps draw new conclusions from it.
As part of the query, you could make any necessary additional reformatting.

 

[Pat Hartman]

Going back to the first error. It tells you that there is a data problem. Doing an import without the spec confirms that. The earlier suggestion to not use a y/n data type will solve the problem. There is probably a row with a null value. Changing to integer will allow nulls unless you set the field to require a value.

Go back and do the import without a spec again, what data type did Access assign to the field?

 

[Lateral]

Thanks for all of the suggestions guys, I will get back onto this in a few days time and let you know the outcome.

Cheers
Greg

 

[arnelgp]

you can try to use a function to import the textfile.
see this demo.

 

[Lateral]

you can try to use a function to import the textfile.
see this demo.

Thanks for this but I cannot open the file. I get the following error:

 

[Pat Hartman]

Are you trying to open the app with an old version of Access?

 

[Lateral]

Going back to the first error. It tells you that there is a data problem. Doing an import without the spec confirms that. The earlier suggestion to not use a y/n data type will solve the problem. There is probably a row with a null value. Changing to integer will allow nulls unless you set the field to require a value.

Go back and do the import without a spec again, what data type did Access assign to the field?

Hi Pat,

This is what I have just done:

1. The [AvailOrder} field in the Web_Parts table is defined as a YES/NO field.

2. I then ran an import (via the External Data/text option) using a single record without a Specification and it worked perfectly and the data from the AvailOrder field in the CSV field was imported correctly into the corresponding AvailOrder YES/NO field in the Web_Parts table.

3. I then ran the import again (via the External Data/ text option) using a larger CSV file (1887 records) without a Specification and it also imported all records including the AvailOrder data correctly into the Web_Parts table.

Are you trying to open the app with an old version of Access?

Hi Pat

I'm using Access 2007

 

[Lateral]

then don't use any import/export specification for your import:

DoCmd.TransferText acImportDelim, , "Web_Parts", "C:\testmiport.csv", True, ""

It appears that the acImportDelim option needs a Specification otherwise you get the following error:

 

[Lateral]

It appears that the acImportDelim option needs a Specification otherwise you get the following error:

 

[Pat Hartman]

Back to the original question --- did you do what I asked you to do two days ago? What was the result? It is really frustrating to try to debug remotely under the best of circumstances. arne is not bothering to debug. He is offering a function. I didn't look at it because you need to figure out if you have a data problem and then decide how you want to fix the data problem. Not sure why a function will solve your problem but whatever. If it turns out to be a data issue, you can resolve it as I suggested or you can use an Nz() to replace null with false or true, whichever you want as your default.

 

[Lateral]

Hi Pat,

I re-ran the import via the External Data/text option with using a Specification and only specifying the "First Row Contains Field Names" and the text Qualifier. I then had it create a new table. All of the fields of the new table were TEXT.

I then ran the import again without a Specification and only specifying the "First Row Contains Field Names" and the text Qualifier and pointed it to the Web_Parts table that has the fields defined as YES/NO etc and it works fine and all of the data is imported correctly.

 

[Pat Hartman]

Go back and do the import without a spec again, what data type did Access assign to the field?

And what is the data type of the Yes/No fields when you import the file without a spec? Are they STILL Y/N???? Please go back and read #10 again. I can't see your file.

Also, you can link the spreadsheet. Then view it and sort on the new Y/N column. First ascending and then descending. Does EVERY row have a value?
This is like pulling teeth.