Csv import not working

Question

Hi

In a legacy Access 2000 app when I import a csv file that has all column data enclosed within double quotes then all columns are imported as text. How can I get Access to import data correctly, for example in below csv I need first and second columns as datetime, third as number etc.

Thanks

Regards

"08/01/2018 00:00:00","08/01/2018 23:59:59","2","Cube Ice Standard 12kg","1","1","Each","14"

Answer 1

Can't you do that by creating an "Import Specification"?

---

-Tom. Microsoft Access MVP

Answer 2

In a legacy Access 2000 app when I import a csv file that has all column data enclosed within double quotes then all columns are imported as text. How can I get Access to import data correctly, for example in below csv I need first and second columns as datetime, third as number etc.

Hi Yahya,

The surrounding with double quotes is probably (automatically) done to distinguish between between separating comma's and "normal" comma's within a text field.

As csv files are just textfiles you could manipulate the lines by converting the doublequote-comma-doublequote (",")  to a semicolon (;), and stripping the double quotes at the beginning and the end of the line. Then use the semicolon as separator.

Imb.

Answer 3

Yes, you would need either need to create an import spec in Access as Tom mentioned or manually create a schema.ini file that defines the data type for each column.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

As csv files are text, I do this in 2 steps

Import the csv file to a buffer table where all fields have datatype text.

Create a procedure to append the data to the final table using functions like CDate, CDbl etc to convert the datatype of individual fields as necessary. Suggest using Nz as well to prevent errors if a record in the field to be converted is null

You can empty the buffer table once this has been done

Answer 5

Hi Y a h y a,

You had mentioned that you are using legacy Access 2000. As it is a not supported currently , I made a test with Access 2016.

I try to create a file like yours.

then when i follow the import steps then it will show me window below.

where, you can set the desired data type for the columns.

in the next step, it also lets you to view and modify the data type in dialog below.

so after importing the data , it displayed correctly in data type that you had set before.

So if you are available with newer version of Access then you can follow above mentioned approach.

Or , if you are not available with latest version then you can try to use DoCmd.TransferText Method in vba.

DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", _
    FileName:="C:\MyData.csv", HasFieldNames:=true

Reference:

DoCmd.TransferText Method (Access)

Further, you can just try to remove the double quotation from dates and then try to import data to check whether it works in Access 2000 or not.

you can also try to create a table first in Access with desired data types and then try to import data to that table. so data will be enter according to that specific data type.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi Y a h y a,

Is your issue solved?

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].