ADO - csv file to Access database 
Author Message
 ADO - csv file to Access database

Hi,

Need some help here plz

I upload a CSV file into Access database via ADO.

Here is the code:

Set Cn = New ADODB.Connection
  Set Rs = New ADODB.Recordset

conString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                        "Dbq= " & strSAPFLoc & ";" & _
                        "DefaultDir=" & strSAPFLoc & ";" & _
                        "Extensions=asc,csv,tab,txt;" & _
                        "HDR=YES;" & _
                        "Persist Security Info=False"

Cn.Open conString

strTextSQL = "select [Order],[Item],[Sch Line],[Ship-to], " _
& "from [" & strSAPFName & "]"

strSQL = "INSERT INTO [SAPinfo] IN '" & dbPath & "'" _
& " " & strTextSQL & ""

Rs.Open "Delete * from [SAPinfo] IN '" & dbPath & "'", Cn

  Rs.Open strSQL, Cn

--------------------------------------------------------------------

The problem I'm having is that I have more fields in destination table
SAPinfo than I've got in the CSV file, therefore I can't say "Select *
from CSVfile" and need to select specific fields like
[Order],[Item],...etc (that's what strTextSQL is doing).When I select
"Select [Order],[Item],....from CSVfile" I get an error "Circular
reference caused by alias...."

Can I refer to specific fields by their position? I tried to call them
"select F1,F2,F3...FROM CSVfile" and get the message "too few
parameters.Expected 32"

TIA. Appreciate your help.



Sun, 27 Nov 2005 08:24:48 GMT  
 ADO - csv file to Access database
Hi,

You need to include a destination field list as well as a source field list.

strTextSQL = "select [Order],[Item],[Sch Line],[Ship-to], " _
& "from [" & strSAPFName & "]"

strSQL = "INSERT INTO [SAPinfo] (Order, Item, [Sch Line], [Ship-To] IN '" & dbPath & "'" _
& " " & strTextSQL & ""

Adelle.
Neat tools.  Lousy slogan.
http://www.akemi..com.au/vb



Sun, 27 Nov 2005 16:08:27 GMT  
 ADO - csv file to Access database

Hi,

Need some help here plz

I upload a CSV file into Access database via ADO.

Here is the code:

Set Cn = New ADODB.Connection
  Set Rs = New ADODB.Recordset

conString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                        "Dbq= " & strSAPFLoc & ";" & _
                        "DefaultDir=" & strSAPFLoc & ";" & _
                        "Extensions=asc,csv,tab,txt;" & _
                        "HDR=YES;" & _
                        "Persist Security Info=False"


Cn.Open conString

strTextSQL = "select [Order],[Item],[Sch Line],[Ship-to], " _
& "from [" & strSAPFName & "]"

strSQL = "INSERT INTO [SAPinfo] IN '" & dbPath & "'" _
& " " & strTextSQL & ""

Rs.Open "Delete * from [SAPinfo] IN '" & dbPath & "'", Cn

  Rs.Open strSQL, Cn

--------------------------------------------------------------------



The problem I'm having is that I have more fields in destination table
SAPinfo than I've got in the CSV file, therefore I can't say "Select *
from CSVfile" and need to select specific fields like
[Order],[Item],...etc (that's what strTextSQL is doing).When I select
"Select [Order],[Item],....from CSVfile" I get an error "Circular
reference caused by alias...."

Can I refer to specific fields by their position? I tried to call them
"select F1,F2,F3...FROM CSVfile" and get the message "too few
parameters.Expected 32"

The following example works for me although I'm using the generic column names. If the first row
contains your column names (HDR=Yes), or you are using a schema.in file, then you need to specify
those as your column names:

Sub ImportTextToAccessADO()

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlString As String

cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=e:\My Documents\DB1.mdb;" & _
                     "Jet OLEDB:Engine Type=4;"

sqlString = "INSERT INTO [tblOrder] (Field1, Field2, Field3)  SELECT F1, F2, F3 FROM
[Text;DATABASE=e:\My Documents\TextFiles;].[Order.txt]"

cnn.Execute sqlString

End Sub


Microsoft MVP (Visual Basic)



Sun, 27 Nov 2005 22:01:40 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Accessing XLS, CSV, and tab-delimited files, etc etc using ADO

2. Import CSV file into Access table using ADO

3. Problems accessing data from a CSV file with ADO

4. Problem importing CSV file into an Access Database

5. Importing csv file into access database using Visual Basic

6. Attaching Text CSV file to Access Database

7. CSV into Access 97 using ADO and some basics

8. Connecting to a CSV file using ADO.NET

9. Using ADO to process a CSV file

10. ADO and Text / CSV Files?

11. ADO to connect to CSV file

12. Opening CSV file of more than 256 columns using ADO

 

 
Powered by phpBB® Forum Software