Import csv file into external DB

[UHNSTrust]

I have a front end that I use to run various functions.

From the front end I want to run code that will import a csv file into a table in another DB. I can do it into the same DB but not sure where to start in an external DB.

Can somebody help? Is it possible?

Thanks in advance.

Jonathan

 

[JerryKlmns]

Yes Jonathan it is and I love it so much!!

My way :
Open a connection to the other db, link the csv file to the other db (I use ADOX and Schema.ini file to accomplish that) and execute code for importing throu the connection!

If you need more on how to, just post .....

 

[UHNSTrust]

Jerry,

Could you post some example code please. Not used connections before.

Thanks

Jonathan

 

[JerryKlmns]

Openning connection.....
Dim App_Cnn As ADODB.Connection

Set App_Cnn = New ADODB.Connection
With App_Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = App_Folder & App_Dbase
'For user level security uncomment the following lines
' .Properties("Jet OLEDB:System database") = App_Folder & Sys_Dbase
' .Properties("User ID") = PowerUser
' .Properties("Password") = PowerPassword
' .Properties("Persist Security Info") = False
.Properties("Mode") = adModeReadWrite
.Properties("Jet OLEDB:Engine Type") = 5
.Properties("Locale Identifier") = 1033
.Open
End With

Closing & cleanning
App_Cnn.Close
Set App_Cnn = Nothing
------------------------------------------------------
Linking Procedure

Sub ImportSchema(Pinakas As String)

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

'Linking new file to DB
Set cat.ActiveConnection = App_Cnn
Set tbl.ParentCatalog = cat

With tbl
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = "Text"
.Properties("Jet OLEDB:Link Datasource") = Data_Folder
.Properties("Jet OLEDB:Remote Table Name") = Pinakas & ".txt"
.Name = "N_" & Pinakas
End With

cat.Tables.Append tbl
cat.Tables.Refresh

'Clearing old records of the table
App_Cnn.Execute "Delete " & Pinakas & ".* From " & Pinakas & ";", adExecuteNoRecords, adCmdText

'Insterting new records
App_Cnn.Execute "Insert into " & Pinakas & " Select N_" & Pinakas & ".* From N_" & Pinakas & ";", adExecuteNoRecords, adCmdText

'Unlinking file
cat.Tables.Delete "N_" & Pinakas

'Clear objects
Set tbl = Nothing
Set cat = Nothing

End Sub

------------------------------------------------------
Rest code

For any recordsets use
rst.ActiveConnection=App_Cnn

For bulk transactions use
App_Cnn.Execute sqlCode, adExecuteNoRecords, adCmdText

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

Explaination of variables:

App_Folder ---> Folder of This database, .mdw, 2nd database
App_Dbase ---> The name of This database
Sys_Dbase ---> The name of .mdw file
PowerUser ---> A user for read/write/delete/update etc
PowerPassword --->His password
Pinakas ---> The table name to import new records (olds are deleted)
Data_Folder --> Folder of Schema.ini file and new file to import

Schema.ini ---> File containing record layout of the importing file

Looks like
------------------------------------------------------
[Ecl1.txt] -----> Filename to be imported, Ecl1
is also a table name in the 2nd database
ColNameHeader = False
Format = Delimited(|)
MaxScanRows=0
CharacterSet = ANSI
DateTimeFormat=yyyy-mm-dd
DecimalSymbol=,
TextDelimiter=None
NumberLeadingZeros=True
Col1=Product Integer
Col2=Subproduct Integer
Col3=Status Char Width 1
Col4=Status_Discript Char Width 255
Col5=BusUnit Integer
Col6=Bank Integer
Col7=Center Integer
Col8=Main Double
Col9=Chkdgt Integer
Col10=Last_Status_Date Date
.
.
.

[Sbc1.txt] ---> Like Ecl1.txt but of different structure
ColNameHeader = False
CharacterSet = OEM
Format = FixedLength
FixedFormat=TrueFixedLength
DateTimeFormat=yyyymmdd
TextDelimiter=None
Col1=Branch Integer Width 3
Col2=Cncy Integer Width 4
Col3=Account Double Width 13
Col4=FirstName Char Width 20
Col5=LastName Char Width 40
Col6=Valid DateTime Width 8
.
.
.
------------------------------------------------------

In order to write your schema.ini file use notepad.

 

[UHNSTrust]

Jerry this looks great.

I will have a go.

Thanks

Jonathan

 

[JerryKlmns]

Forgot to tell you a crucial tip. In VBA Tools-->References check your

Microsoft ActiveX Data Object 2.x Library
Microsoft ADO Ext. 2.x for DDL and Security

versions.

 

[UHNSTrust]

Jerry,

Tried this out but where does the schema.ini come into it? Everything else works but the import specs.

Jonathan

 

[JerryKlmns]

The Schema.ini is in folder Data_Folder where there is also the importing file.

You have to give the record layout description of the importing file, field by field like

Col8=Main Double

col8 ---> is field 8 of record layout in file

Main--> is the field name in table structure

Double or Integer or Date or Char --> is the data type corresponding to the field in the table where it is to be imported (Char is the equivalent of Text in database).

Examples Given
File [Ecl1.txt] |Database Table Ecl1
Field Name |Field Name |Data Type |Field Size
-----------------+----------------+-----------+----------
Col1 |Product |Integer |Integer
Col2 |Subproduct |Integer |Integer
Col3 |Status |Text |1
Col4 |Status_Discript |Text |50
Col5 |Product |Integer |Integer
.
.
.

Sbc1.txt is not delemitid with some character, so the record layout in notepad look like a lines of the same length.Fields ara now destinguished by exact length

File [Sbc1.txt] |Database Table Sbc1
Field Name |Size |Field Name |Data Type |Field Size
-----------+-----+-------------+-----------+------------
Col1 | 3 |Branch |Integer |Integer
Col2 | 4 |Cncy |Integer |Integer
Col3 | 13 |Account |Double |13
Col4 | 20 |FirstName |Text |20
Col5 | 40 |LastName |Text |40
Col6 | 8 |Valid |Date |
.
.
.