Solved

VBA to Import a csv or xls file ONLY as text

Posted on 2010-09-09
9
1,508 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now