[Webinar] Streamline your web hosting managementRegister Today


Loading multiple CSV and Text files into a database, without importing header rows

Posted on 2005-03-23
Medium Priority
Last Modified: 2013-12-25
I would like to see a few examples of how to import multiple CSV and text files into the one table, only importing specific data that i specify.

The CSV and Text files have column names in common, but not the same as the database. They are varied, and I only want to place certain columns in the database table.

So I need to say as below

Text1.csv has these columns
PCode, Suburb, Temp, Min, Max, Day, Month, Highest, Lowest

I only want:

PCode, Suburb, Temp, Min and Max

Into these database (saejdb1.mdb) fields

PostCode, Suburb, Temperature, Minimum, Maximum

Continuing through the one text file til it reaches the end.

I do know a bit about VB programming, but one can only know so much, so if anyone could point me to any direction on where to start and whats going to work best, please do let me know.

Need to be able to specify the delimiter, and the type of file. All I need is an example of how to do this.

If you need further information about this please do contact me.

Question by:TOC-Fried
LVL 53

Expert Comment

ID: 13609615
Take a look at the following example. Can be a good start
LVL 16

Expert Comment

ID: 13616813
You can use ADO to open a delimited text file, and if you have a header row you get the added bonus that your columns will be named.

This connection string will open a delimited file as below
Dim cn As New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & PathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited(~)"""

My file looks like

8~Couch~john doe~56 Lakeview~Lawton~OK~73507~580-555-1212~8~Brox Industriea Inc~123 nowhere lane~Lawton~OK~73501~580-353-3878~35715~348020~Big company~92951~OKLAHOMA CITY~321 somewhere lane~~~OKLAHOMA CITY~OK~73107~1334279~03-JAN-05~1~2~CT~20-JAN-05~20-JAN-05~G0HSRGC11875 XX~CUSTOM 11.875X150F~229638~


Author Comment

ID: 13616961
Dhaest, your link was to download a full version of somebody else's work.

I saw what was in that and it wasn't suitable for the task, it only picks all records, and inserts into one cell in the database.

How would I specify the file in the connection string you provided JohnBPrice?

Also I need to get around how to select what records in the newly created recordset, (the text file) and insert them into different named fields in an access database.

We're getting there.

To reiterate what I need is to select 1 text file by name and sort that into only the fields I need, and insert those needed fields into the database. I started this app thinking it would be simple enough,
I'm hoping it will be!
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

ID: 13618337
The steps for this as I can make out are:

Create a connection to the text file
Dim cn As New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & PathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited(~)"""

create a record set from the connection

<-- how do I do this -- >

Create another connection to the database
Dim cn1 As New ADODB.Connection
  cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" DATABASE LOCATION "

Insert the records from the text file into the database

<-- why isn't there any information around on this? -->

It would be much appreciated if anyone could add to this for me. Anything. Thanks In Advance.

Expert Comment

ID: 13620472
Below is a simple example for opening a csv file, processing the records, and moving them into an access data

Private Sub Open_CSV()
Dim fcnt As Integer
Dim myConn As New ADODB.Connection
Dim myDB As New ADODB.Recordset

myConn.CursorLocation = adUseClient
myConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDatabase.mdb;Persist Security Info=False"
myDB.Open "select * from myTable", myConn, adOpenDynamic, adLockOptimistic

' Open the CSV file. Note that this CSV file has 6 columns.

Open "c:\mycsv.csv" For Input As #1

    Do While Not EOF(1)
        fcnt = 0
            Do Until fcnt = 6                                   ' Adjust this value dependent on the number of columns in your CSV file
                Input #1, csvVal
                    Select Case fcnt
                          Case 0
                          myDB.Fields(3) = csvVal          ' Move the csv values into your database. If you want to ingore a
                          Case 1                                   ' particular column in your CSV file, don't include it in the select case
                          myDB.Fields(1) = csvVal          ' statement.  Note that you can move any of the CSV values into
                          Case 2                                   ' any of the Access columns, you are not restricted to moving the
                          myDB.Fields(2) = csvVal         ' first CSV column into the first Access column etc etc.
                          Case 3
                          myDB.Fields(0) = csvVal
                          Case 4
                          myDB.Fields(4) = csvVal
                          Case 5
                          myDB.Fields(5) = csvVal
                    End Select
                fcnt = fcnt + 1

' Tidy up
close #1

End Sub


Expert Comment

ID: 13620547
Try this one also,

Private Sub cmdCSV_Click()
    Dim cn As New ADODB.Connection
    Dim PathtoTextFile  As String
    PathtoTextFile = App.Path & "\Book1.csv"
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\csvdb.mdb;Persist Security Info=False"
    ImportTextFile cnn, "csvTable", PathtoTextFile, "0,1,3,7,8", ",", , True
End Sub

The above procedure for calling the following function.


Public Function ImportTextFile(cn As Object, _
  ByVal tblName As String, FileFullPath As String, _
  ByVal sRequiredFieldsPosition As String, _
  Optional FieldDelimiter As String = ",", _
  Optional RecordDelimiter As String = vbCrLf, _
  Optional IsHeaderRowExist As Boolean = True) As Boolean
'PURPOSE: Imports a delimited text file into a database

'PARAMTERS: cn -- an open ado connection
'          : tblName -- import destination table name
'          : FileFullPath -- Full Path of File to import form
'          : FieldDelimiter -- (Optional) String character(s) in
'                              file separating field values
'                              within a record; defaults
'                              to ","
'          : RecordDelimiter -- (Optional) String character(s)
'                                separating records within text
'                                file; defaults to vbcrlf
'           : IsHeaderRowExist -- (optional) Boolean
'                                 True  = Omitt the First Row, False -- Adds the First Row

'RETURNS: True if successful, false otherwise
'dim cn as new adodb.connection
'cn.connectionstring = _
'   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
'ImportTextFile cn, "MyTable", "C:\myCSVFile.csv"


Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sFileContents As String
Dim iFileNum As Integer
Dim sTableSplit() As String
Dim sRecordSplit() As String
Dim lCtr As Integer
Dim iCtr As Integer
Dim iFieldCtr As Integer
Dim lRecordCount As Long
Dim iFieldsToImport As Integer
Dim lCtrSt  As Integer
Dim intR    As Integer

'These variables prevent
'having to requery a recordset
'for each record
Dim asFieldNames() As String
Dim abFieldIsString() As Boolean
Dim iFieldCount As Integer
Dim sSQL As String
Dim bQuote As Boolean

Dim asRequiredFields() As String

On Error GoTo errHandler
If Not TypeOf cn Is ADODB.Connection Then Exit Function
If Dir(FileFullPath) = "" Then Exit Function

If cn.State = 0 Then cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = tblName
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
iFieldCount = rs.Fields.Count

ReDim asFieldNames(iFieldCount - 1) As String
ReDim abFieldIsString(iFieldCount - 1) As Boolean

For iCtr = 0 To iFieldCount - 1
    asFieldNames(iCtr) = "[" & rs.Fields(iCtr).Name & "]"
    abFieldIsString(iCtr) = FieldIsString(rs.Fields(iCtr))

iFileNum = FreeFile
Open FileFullPath For Input As #iFileNum
sFileContents = Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
'split file contents into rows
sTableSplit = Split(sFileContents, RecordDelimiter)
lRecordCount = UBound(sTableSplit)
'make it "all or nothing: whole text
'file or none of it

asRequiredFields = Split(sRequiredFieldsPosition, ",")

lCtrSt = 0
If IsHeaderRowExist Then lCtrSt = 1
For lCtr = lCtrSt To lRecordCount - 1
        'split record into field values
    sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
    'iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
        iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)
    iFieldsToImport = UBound(sRecordSplit) + 1
   'construct sql
    sSQL = "INSERT INTO " & tblName & " ("
    intR = 0
    For iCtr = 0 To iFieldsToImport - 1
        'bQuote = abFieldIsString(iCtr)
        If (InStr(1, sRequiredFieldsPosition, CStr(iCtr)) > 0) Or (sRequiredFieldsPosition = "") Then
            sSQL = sSQL & asFieldNames(intR)
            intR = intR + 1
            If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
        End If
    Next iCtr
    sSQL = sSQL & ") VALUES ("
    intR = 0
    For iCtr = 0 To iFieldsToImport - 1
        If (InStr(1, sRequiredFieldsPosition, CStr(iCtr)) > 0) Or (sRequiredFieldsPosition = "") Then
            If abFieldIsString(intR) Then
                 sSQL = sSQL & prepStringForSQL(sRecordSplit(iCtr))
                sSQL = sSQL & sRecordSplit(iCtr)
            End If
            intR = intR + 1
            If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
        End If
    Next iCtr
    sSQL = sSQL & ")"
    cn.Execute sSQL

Next lCtr


Close #iFileNum
Set rs = Nothing
Set cmd = Nothing

ImportTextFile = True
Exit Function

MsgBox Err.Description
On Error Resume Next
If cn.State <> 0 Then cn.RollbackTrans
If iFileNum > 0 Then Close #iFileNum
If rs.State <> 0 Then rs.Close
Set rs = Nothing
Set cmd = Nothing

End Function

Private Function FieldIsString(FieldObject As ADODB.Field) _
   As Boolean
     Select Case FieldObject.Type
         Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _
               adLongVarChar, adLongVarWChar
               FieldIsString = True
            Case Else
               FieldIsString = False
        End Select
End Function

Private Function prepStringForSQL(ByVal sValue As String) _
   As String

Dim sAns As String
sAns = Replace(sValue, Chr(39), "''")
sAns = "'" & sAns & "'"
prepStringForSQL = sAns

End Function


Author Comment

ID: 13620952
Ok, that gives me a INSERT INTO error, so something isn't right there?

Isn't there a nice simply way? Where i can say import column a, b ,c, d and e into this table as an append?
there has to be a better way! It's Microsoft we're talking about ere.

Author Comment

ID: 13621008
ajgilbert ,

Your solution seems like what I am looking for, Just a quick question first:

How do I say which fields from the CSV to import. And. will this work on tab text files etc?

Author Comment

ID: 13636164
When will I see a response to this?
LVL 16

Expert Comment

ID: 13636600
how about this, you'll have to change the paths & filenames to match yours.  
This assumes the text file has a header row, if it doesn't you can use column positions instead, e.g. rstext.fields(0)

    Dim dbText As New ADODB.Connection
    Dim dbAccess As New ADODB.Connection
    Dim rstext As New ADODB.Recordset
    Dim rsaccess As New ADODB.Recordset
    dbText.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\;Extended Properties=""text;HDR=YES;FMT=Delimited(,)"""
    rstext.Open "select * from MyInputFile.csv", dbText, adOpenStatic, adLockReadOnly
    dbAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\saejdb1.mdb"

    rsaccess.Open "Select * from OutPutTable where 1=2", dbAccess, adOpenStatic, adLockOptimistic
    While Not rstext.EOF
        rsaccess.Fields("PostCode") = rstext.Fields("PCode")
        rsaccess.Fields("Suburb") = rstext.Fields("Suburb")
        rsaccess.Fields("Temperature") = rstext.Fields("Temp")
        rsaccess.Fields("Minimum") = rstext.Fields("Min")
        rsaccess.Fields("Maximum") = rstext.Fields("Max")

Author Comment

ID: 13637771
That works perfectly, thanks heaps for that.

How would I in the code above handle a item which has no value? ie.. if the cell value has nothing in it?.

I got an error on one of the sheets which was telling me the empty cell is a problem.
LVL 16

Expert Comment

ID: 13643765
If your target database does not allow nulls in a column, you'd have to convert nulls into something.  
Making nulls into an empty string is common, and you can use

Public Function NullAsBlank(dbfield As ADODB.field) As String
    If IsNull(dbfield.value) Then
        NullAsBlank = ""
        NullAsBlank = Trim(dbfield.value)
    End If
End Function

and then change the code that copies the field in question, e.g. if the field was PostCode, use
       rsaccess.Fields("PostCode") = NullAsBlank(rstext.Fields("PCode"))


Author Comment

ID: 13644869
That was the easy bit to work out, but i ain't gonna go through coding a on Null event for 30 different imports.
Can I make the database except nulls? That would be pure genious. I want this database to take exactly what is thrown at it? Possible?

The exact error is:

In the tect file specification 'mysheetname.csv'. the Col2 option is invalid.

Runtime error: -2147467259 (80004005) Any ideas on that? I assume it's saying column two, nothing on this at google though,
LVL 16

Expert Comment

ID: 13661148
That doesn't look like a null issue, it looks like a problem reading the data.  
Do you get that error on the first row, or do you get through some rows before it throws the error?

Can you post the first few lines of you .csv file?

Author Comment

ID: 13661701
Doesn't get any rows done. None what so ever! :(

That's the header row, and i think some of the fields. Other parts like descriptions have squares like emulating the user hitting SHIFT AND ENTER to get a new line etc. But, I am postive the others have this same thing, so it wouldn't be that. At least I don't think it is.

56,"SYSKONNECT","Network Interface Adapters","SysKonnect PCI Gigabit Ethernet Adapters","Fibre Gigabit Multi Mode Ethernet Cards","SK-9843-V2","PCI 64-bit 1000Base-SX (SC) Managed Fibre Optic Adapter. Max. range 220m (62.5/125) or 550m (50/125) V2.0"

I can screenshot and edit some of the price based fields for you to see if you like.

My code on the import is this:

        rsaccess.Fields("Code") = rstext.Fields("PRODUCT_CODE")
        rsaccess.Fields("Description") = rstext.Fields("PRODUCT_DESCRIPTION")
        rsaccess.Fields("Buy") = rstext.Fields("BUY_D3")
        rsaccess.Fields("Stock") = rstext.Fields("STOCK")
        rsaccess.Fields("ETA") = "-"

Which all seems to be well and good for the others. Let me know what else you need!

Author Comment

ID: 13747446
No result seems to have spawned from this. I am still chasing an answer to my question...

Author Comment

ID: 13763326
Still nothing, is anyone going to add some input here, I won't be giving points to those that abandoned the question! :(
LVL 16

Expert Comment

ID: 13765110
Well, I don't quite understand your angst.  
I posted a solution to your original question, which you said "works perfectly, thanks heaps for that."

Your second question (which isn't quite kosher, see http://www.experts-exchange.com/help.jsp#hi107) was that you were getting
errors when reading an ASCII text file with non-ASCII characters in it.  

That not withstanding, from the sample you posted it would appear the non-ascii text causing the problem is in the
BUY_D3 field, since that is the first field missing from the bit you posted.

Author Comment

ID: 13768666
Welcome Back John,

The BUY_D3 column has $100.00 in it.
So Assuming that all those characters are indeed ASCII based, I don't understand the error.
And as per the link you posted, the original question is yet to be answered, I am looking for something that will load multiple CSV and Text Tab into a database.
None of the solutions proposed have worked in their entireity so I am assuming that the original question as still been unanswered.

Still need to be able to specify a TAB delimiter as well? None of the solutions posted so far are acceptable enough. Your's is the best match, that's why I have been awaiting response.
LVL 16

Accepted Solution

JohnBPrice earned 640 total points
ID: 13772508
In your sample above, the BUY_D3 (and everything after that) is missing.  Maybe the "$" is fouling it up?

You can specify most delimiters except tab with the code above.  To specify a tab delimiter (due to some bug in MDAC)
you must create a schema.ini AND use FMT=TabDelimited

The schema.ini must be in the same directory as your file and should look like this

The code I use to automatically build the schema.ini and open a tab delimited csv is...
    Dim dbSource as New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fs As New FileSystemObject
    Dim file As Scripting.file
    Dim SchemaFile As Scripting.TextStream

    'due to some weird bug, we have to create a schema.ini because the FMT=TabDelimited doesn't work
    Set file = fs.GetFile(txtFileName.Text)
    Set SchemaFile = fs.OpenTextFile(file.ParentFolder & "\schema.ini", ForWriting, True)
    SchemaFile.WriteLine "[" & file.Name & "]"
    SchemaFile.WriteLine "Format=TabDelimited"

    dbSource.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file.ParentFolder & ";Extended Properties=""Text;HDR=YES;FMT=TabDelimited;"""
    rs.Open "select * from [" & file.Name & "]", dbSource, adOpenStatic, adLockReadOnly

Expert Comment

ID: 20478764
Here is what I found to be the problem with the schema.ini
that is still true in 2007.

When the ODBC Text driver creates the Schema.ini - it creates the follwoing
Col1=ID Integer
Col2=FUNDNAME   (It leaves off the Field Type)

When trying to access the file you get the following error : Col2 option is invalid

to fix :

It adds Integer, Currency, Date, etc. but leaves off Char.


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question