import separated column from csv

[sal21]

I have a csv delimited from ";" .
Into csv file are 8 column.
I need to import via ado and vb into a access table, with this condition:

Column2 csv into column7 access table
Column8 csv into column2 access table
Column4 csv into column9 access table
....
How to?

[HansV]

Does the Access table already exist, or do you want to create a new table?

[sal21]

Does the Access table already exist, or do you want to create a new table?

just exist and is named Table01

[HansV]

Try code like this:

Dim cnn As New ADODB.Connection
Dim strSQL As String

' Your code to open a connection to the database
...
' Insert records from the text file
strSQL = "INSERT INTO Table01 (Field7, Field2, Field9) SELECT Field2, Field8, Field4 FROM [Text;Database=C:\TextFiles].[Test.csv]"
cnn.Execute strSQL

Of course, you must substitute the correct names of the fields, as well as the correct path and filename of the .csv file.

[sal21]

Try code like this:

Dim cnn As New ADODB.Connection
Dim strSQL As String

' Your code to open a connection to the database
...
' Insert records from the text file
strSQL = "INSERT INTO Table01 (Field7, Field2, Field9) SELECT Field2, Field8, Field4 FROM [Text;Database=C:\TextFiles].[Test.csv]"
cnn.Execute strSQL

Of course, you must substitute the correct names of the fields, as well as the correct path and filename of the .csv file.

Hans before to test code... but the code consider the first line of csv as title of columns?
In other case i dont want to import the first line of csv into the table:-)

[HansV]

The code assumes that the first line of the CSV file contains field names. You should use those field names in your code (in the SELECT Field2, Field8, Field4 FROM ... part)

[sal21]

The code assumes that the first line of the CSV file contains field names. You should use those field names in your code (in the SELECT Field2, Field8, Field4 FROM ... part)

OK for fields name.
But have "invalid path name" error????????? why????


strSQL = "INSERT INTO GAF (NDG, SPORTELLO, RAPPORTO) SELECT NDG, SPORTELLO, RAPPORTO FROM [Text;Database=C:\dir1\csv_prodotti_TEST].[Test.csv]"

Debug.Print strSQL
GAF_CONN.Execute strSQL

[HansV]

The first part between square brackets should be the path, and the second part the filename, so you should use

[Text;Database=C:\PETTI].[csv_prodotti_TEST.csv]

[sal21]

Try code like this:

Dim cnn As New ADODB.Connection
Dim strSQL As String

' Your code to open a connection to the database
...
' Insert records from the text file
strSQL = "INSERT INTO Table01 (Field7, Field2, Field9) SELECT Field2, Field8, Field4 FROM [Text;Database=C:\TextFiles].[Test.csv]"
cnn.Execute strSQL

Of course, you must substitute the correct names of the fields, as well as the correct path and filename of the .csv file.

Hans... based this command i need to insert in Table01 a new dta from a variable and not from csv file field... How to?
Example:

strSQL = "INSERT INTO Table01 (Field7, Field2, Field9,Field10) SELECT Field2, Field8, Field4, '" & myvar &"' FROM [Text;Database=C:\TextFiles].[Test.csv]"

[HansV]

What kind of field do you want to insert the data into? Text, number, date, ...?

[sal21]

What kind of field do you want to insert the data into? Text, number, date, ...?

is a date ...

[HansV]

Try

strSQL = "INSERT INTO Table01 (Field7, Field2, Field9,Field10) SELECT Field2, Field8, Field4, #" & Format(myvar, "mm/dd/yyyy") & "# FROM [Text;Database=C:\TextFiles].[Test.csv]"

[sal21]

Try

strSQL = "INSERT INTO Table01 (Field7, Field2, Field9,Field10) SELECT Field2, Field8, Field4, #" & Format(myvar, "mm/dd/yyyy") & "# FROM [Text;Database=C:\TextFiles].[Test.csv]"

oK!
i cannot test now because have a prob on delimited separato....
In effect i dont have ; in csv but ";"

with this type of separator work
qqqqq;eseeee;......

i really have this structure of csv file
"qqqqq";"eseeee";"......