[AccessD] converting csv to table

Thanks, I'll declare aRecord as string. Its this part I am having trouble
with

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

Where do I specify to what table, and to what fields (in this database) do
I want records to be inserted ?
Also, Access97 doesn't have split function ? I did that part at home on
Access2000

> Some ideas:
>
> I assume what you are saying is that the date is stored in your csv
> table as yyyymmdd and access is reading it as mmddyyyy (ie american date
> format) and thus your dates are screwed up.
>
> Using dim aRecord as you have will default the variable to a variant.
> (A side note, I always explicity declare, that way I avoid unexpected
> surprises if MS decides to change the default behaviour)
>
> I don't know what split does, but I am assuming it tries to guess what
> the variable type is and then stores it as that type.  I would first
> check your PC date format, but I suspect that you will be stuck with US
> interpretation and will have to "manually" force the date to be read the
> correct manner.
>
> First suggestion would be to dim aRecord as a string.  This will force
> the date (and other fields) to be stored as they appear in the csv file.
>  Then you can split the date field using left$/right$/mid$ and datepart.
>
> If I am incorrectly guessing what your problem is, maybe someelse will
> have a better idea.
>
> Stuart
>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>> Oleg_123 at xuppa.com
>> Sent: Wednesday, 15 October, 2003 12:10 AM
>> 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
>>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



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