Solved

VBA to Import a csv or xls file ONLY as text

Posted on 2010-09-09
9
1,611 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:VenturSum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33643369
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
 

Author Comment

by:VenturSum
ID: 33643396
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33643512
<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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 33643558
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 33643851
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
 
LVL 3

Accepted Solution

by:
petelettin earned 300 total points
ID: 33644211
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
 

Author Comment

by:VenturSum
ID: 33644573
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
 
LVL 3

Expert Comment

by:petelettin
ID: 33644830
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
 

Author Closing Comment

by:VenturSum
ID: 34141928
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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