VBA to Import a csv or xls file ONLY as text

Experts,

I need to import either an excel file or a csv /  text file such that the imported table, imports all datatypes as Text and only as Text.  If a number or currency or date or time is imported, it still needs to be shown as and stored as text.

And, I need to do this completely automated via VBA.

Any thoughts?  

I've tried a lot, but don't want to bias your solution.

John
In Annapolis
VenturSumAsked:
Who is Participating?
 
petelettinCommented:
You can use a schema.ini file to define the schema (field data types) of the csv file.

see http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

basically (from above site)

1. The schema information file, must always named as 'schema.ini'.
2. The schema.ini file must be kept in the same directory where the CSV file exists.
3. The schema.ini file must be created before reading the CSV file.
4. The first line of the schema.ini, must the name of the CSV file, followed by the properties of the CSV file, and then the properties of the each column in the CSV file.

For demonstration, consider a CSV file with 5 columns such as Date, First Name, Last Name, Age and Salary. The data structure of these columns are given below

Column Name         Data Type          Width          Format
 Date                          DateTime                             dd-MMM-yyyy
 First Name               Text                       100
Last Name               Text                       100
Age                             Long
Salary                        Double

1. Open a Notepad file.

2. copy and paste the below content in the notepad and replace 'YourCSVFileName.csv' with your CSV file name.

[YourCSVFileName.csv]
ColNameHeader=True
Format=CSVDelimited
DateTimeFormat=dd-MMM-yyyy
Col1=A DateTime
Col2=B Text Width 100
Col3=C Text Width 100
Col4=D Long
Col5=E Double

3. Save the Notepad file as 'schema.ini' in the same location of your CSV file.


Pete :-)
0
 
Rey Obrero (Capricorn1)Commented:
what version of Access are you using?

you will need an import specification to make this automated..

'to create an import specification
'
1. File>get external data>import
2. select in the File of types box   Text files (*.txt etc..
3. select the file
4. in the import text wizard window select  delimited
5. Click advanced
6. in the import specification window
    type the name of the field in the Field Name column
    (here you can use the field names of the destination table, specify data type,
      check the box Skip if you do not want to import the column)


7 click save as, give the specification a name  <-- this is the specification name that you will use in the command line below


   DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True
0
 
VenturSumAuthor Commented:
Capricorn1,

I'm using Access 2007.

In my application, I can identify the file's name.  But I can't touch the file until after it's imported.

One thought was to:
1)  automate importing the file and automate creating the import specification;
2) deleting the imported table;
3) running a query to change the [DataType]  field in [MSysIMEXSpecs] to 10 (10 = Text); and
4) reimporting the file using the updated import specification.

Any thoughts on automatically creating the Import Specification??

John
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
<Any thoughts on automatically creating the Import Specification??>
you have to do this manually..

if you are sure of the import specification name to use,
run this query to get the SpecID

SELECT MSysIMEXSpecs.SpecID, MSysIMEXSpecs.SpecName
FROM MSysIMEXSpecs;


 get the SpecID from table MSysIMEXSpecs based on the name in the SpecName field

then run an update query to update the table "MSysIMEXColumns"  like this, assuming the SpecID is 5

UPDATE MSysIMEXColumns SET MSysIMEXColumns.DataType = 10
WHERE (((MSysIMEXColumns.SpecID)=5));


import the csv file using this command line

  DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True



0
 
Kelvin SparksCommented:
I'd tackle this in a few steps
 
Firstly do the import to a temp table preserviving the existing datatypes - too much hassle trying to alter those on the fly.
Then have a target table with text datatypes and run an append query where the vales are coverted to text in the query (eg CStr(field))
Would be a couple of lines of code - one for the import and one for the query - you might need a couple of clean up routines as well - delete the temp data etc...)
kelvin
0
 
rockiroadsCommented:
vba approach - you could open up the excel file then read the record, create a new record in your access table and copy fields across

Public Sub ReadExcelFileAndCopyToAccessTable()

        Dim conn As New ADODB.Connection
        Dim rsRead As New ADODB.Recordset
        Dim rsRite As New ADODB.Recordset
       
        With conn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=c:\myxls.xls"
            .CursorLocation = adUseClient
            .mode = adModeReadWrite
            .Open
        End With
       
         'Eneter sheet name as table
        rsRead.Open "SELECT * FROM [sheet1$]", conn, adOpenStatic, adLockPessimistic
        rsRite.Open "myaccesstable"

        Do While rsRead.EOF = False
       
            rsRite.AddNew
            rsRite("field1") = rsRead("field1a")
            rsRite("field2") = rsRead("field2a")
            rsRite("field3") = rsRead("field3a")
            rsRite("field4") = rsRead("field4a")
            rsRite.Update
           
            rsRead.MoveNext
        Loop
       
        rsRead.Close
        rsRite.Close
        Set rsRead = Nothing
        Set rsRite = Nothing
        conn.Close
        Set conn = Nothing
End Sub
0
 
VenturSumAuthor Commented:
Fellas,

I'm taking all this into consideration.
I can't create the schema.ini file because I am unable to know or read the csv / txt file before it's imported.

Currently I've imported the files into temp tables, and have created a file showing all fields, data type, order and size.  It think I've got enough to repopulate the [MSysIMEXColumns] and then use that to re-import the data properly.

I'm reading all you each wrote.

John
0
 
petelettinCommented:
Can you use schema.ini with generic field names (which is what you get in code above) and define more than you'll need.

Don't know if this would work as I haven't tried it.

other than that parse the header line and create the table

for each field in line add column...

Pete :-)
0
 
VenturSumAuthor Commented:
The ultimate solution to importing a CSV or TXT file into an Access table was as follows:

1) created a recordset to read only the first row of the csv file.
2) add to a text string the header of the schema.ini file.
3) use this data to create a table definition file
    - storing column #, Column name, data type (10 = text) , width = 255 = max width
4) while looping through the above data,
     -- add the additional rows to the schema.ini file.
     -- create the import SQL statement
5) delete any schema.ini file
6) create a new schema.ini file with the above data
7) execute an append SQL statement, which by default calls the schema.ini file.

enjoy,
It was tough
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.