[AccessD] converting csv to table

I have a mailing list app and regularly need to load new contact information
from magazines and conferences with a variety of formats.  For a one time
load of a comma delimited format, my procedure is as follows:
1.  Import, choosing the new table and assign primary key options.  If some
data is rejected, change all the field definitions to the most permissive
formats: text 255.  The objective is to get all the data into Access any way
I can and deal with format problems later.
2.  Make a copy of the eventual target table to stage the new data.
3.  Make an append query that maps the input data to the formats required
for the new table using name mapping in the query.  Map the Access assiged
Key in the input into the key field in the staging table.  If it takes some
update queries, You can link the tables using the primary keys.
4.  Review it by eye to check for gross errors and repeat until you like the
results.
5.  run a basic append query to add the data to the production table.

If there is a requirement from more inputs of the same formst, you can put
the machinery on a button for the client as follows:
1.  Rename the Access-generated input table to tmptblxxx and keep it.
2.  Go into the saved formats(non-trivial - email if you need to know how)
and change the name to something meaningful.  I'm not sure it matters, but I
change all the field definitions to the most permissive: text 255.
3.  Do code that
	a. empties both the input and staging tables
	b. runs DoCmd.TransferText to bring in the input.
	c. runs your append query to move the data from the input to staging
tables.
	d. runs a normal select query: DoCmd.OpenQuery to let the client see the
data as it will look when appended to his production table.
	e. runs an append query to move the data from the staging to production
table WITHOUT THE PRIMARY KEY AUTONUMBER FIELD, so keys will be assigned
consistent with the production table.  If you need to know the primary key
values assigned so that you can do related tables, you need to do this step
in code.  After the Addnew, but before the Update, you can read the new key
value.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Oleg_123 at xuppa.com
Sent: Tuesday, October 14, 2003 12:10 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] converting csv to table


Hey Group
I have to convert a CSV file to Access table, with one of the records
changing from yearmonthdate(19980123) to monthdateyear(01231998) format.
So I gues I can't use macro for that since it doesn't recognise
yearmonthdate format. I wrote the code below yesterday and tested it at
home on diff table and it worked up till including Debug.Print
aRecord(8)line  then I had trouble sending received data to table. Now it
doesn't work at all.. any suggestions ?


Dim fs As New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim strLine As String
Dim aRecord
Dim strSQL As String
Dim con As New ADODB.Connection

If fs.FileExists("U:\Users\HRIS\oleg\cars testing\trainFROM.csv") Then

    Set ts = fs.OpenTextFile("U:\Users\HRIS\oleg\cars
testing\trainFROM.csv", ForReading)

    Do Until ts.AtEndOfStream

        strLine = ts.ReadLine
        aRecord = split(strLine, ",")

        aRecord(8) = Mid(aRecord(8), 5) & Left(aRecord(8), 4)

        Debug.Print aRecord(8)

        strSQL = "INSERT table value(" & aRecord(0) & "," & aRecord(8) & ")"
        'con.Execute (strSQL)

    Loop
Else
     MsgBox ("file doesn't exist")
End If




-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com