Importing CSV File

[Bill Schanks]

I am trying to import data into A SQL Server Database via an Access
Project (.adp).

Here is a sample of the data downloaded to a CSV File:

02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
....

When I use this:
docmd.TransferText acImportDelim,,"CCYTrans","C:\550417596.csv"

It imports it properly, however it names the table userid.CCYTrans
regardless if I have that table setup or not. I need it to import into
dbo.ccytrans. So that doesn't work. If I name the table dbo.ccytrans
It names the table userid.dbo_ccytrans. An Access bug in my opionion.
I don't want to have users create tables and then move the records to
the real table.

So I tried this code:

-----
Sub sImportTesting()

Dim BookDate, BankCode, AcctType, AcctNum
Dim i As Integer

Const conFileName As String = "C:\550417596.csv"

Open conFileName For Input As #1

Do While Not EOF(1)

Input #1, BookDate, BankCode, AcctType, AcctNum
Debug.Print BookDate
Debug.Print BankCode
Debug.Print AcctType
Debug.Print AcctNum
i = i + 1
If i = 50 Then Exit Do

Loop

Close

End Sub
-----

But is splits the first column into two. Here is the debug window
output:

sImportTesting
2
Jan 2007
01
I

Any ideas on how to get this to work properly?

 

[John Nurick]

Hi Bill,

I've never needed to use an ADP and don't have an instance of SQL server
to hand, so this is just suggestions.

1) Can you execute a SQL statement along these lines?

INSERT INTO CCYTrans
SELECT * TestImport
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#csv
;

2) As for writing VBA to read the file, I've never had much joy relying
on the Input # statement to parse text files. It seems much more
effective to use Line Input # to read the whole line into a string
variable and then parse it yourself.

 

[Bill Schanks]

Thanks, John. I'll try the Insert you suggest. But my gut feeling is
that it won't work, as the SQL Will be executing on the Server. ADP
Files don't have Jet Tables. Just like if I was using DTS or Bulk
Insert, the file would need to be visible to the Database server. That
would take some time to get setup by our security group.

I was using the Line input method, and parsing it. But by Parser is
very simple, and the data has commas within the fields on the csv
file. So it was mis-parsing. Here is my code:
---
Do 'Loop thru all records

Line Input #1, sLineInput
If i = 1 Then GoTo NextRecord 'Skip header

'Parse Data
'Manually parsed, as dates were not properly parsed when 'Input
#1, fld1, fld2, etc...'
'was used. It was splitting it into two flds, causing multiple
problems.

r = 1
StartPos = 1
Do While StartPos < Len(sLineInput)
iDelimPos = InStr(StartPos, sLineInput, ",")
If iDelimPos = 0 Then Exit Do
vDataPoint(r) = Mid$(sLineInput, StartPos, iDelimPos -
StartPos)
vDataPoint(r) = Replace(vDataPoint(r), conQuote, "") 'Remove
quotes, so data can be used
StartPos = iDelimPos + 1
r = r + 1
Loop
---

Could you suggest a better way to parse? I am limited it having a
comma as the delimiter, however the csv file has the offending fields
wrapped in quotes. I just havn't figured a way to accont for the
quotes yet.

Hi Bill,

I've never needed to use an ADP and don't have an instance of SQL server
to hand, so this is just suggestions.

1) Can you execute a SQL statement along these lines?

INSERT INTO CCYTrans
SELECT * TestImport
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#csv
;

2) As for writing VBA to read the file, I've never had much joy relying
on the Input # statement to parse text files. It seems much more
effective to use Line Input # to read the whole line into a string
variable and then parse it yourself.

I am trying to import data into A SQL Server Database via an Access
Project (.adp).

Here is a sample of the data downloaded to a CSV File:

02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
02 Jan 2007 ,"01","I","108123959780" ...
...

When I use this:
docmd.TransferText acImportDelim,,"CCYTrans","C:\550417596.csv"

It imports it properly, however it names the table userid.CCYTrans
regardless if I have that table setup or not. I need it to import into
dbo.ccytrans. So that doesn't work. If I name the table dbo.ccytrans
It names the table userid.dbo_ccytrans. An Access bug in my opionion.
I don't want to have users create tables and then move the records to
the real table.

So I tried this code:

Dim BookDate, BankCode, AcctType, AcctNum
Dim i As Integer

Const conFileName As String = "C:\550417596.csv"

Open conFileName For Input As #1

Do While Not EOF(1)

Input #1, BookDate, BankCode, AcctType, AcctNum
Debug.Print BookDate
Debug.Print BankCode
Debug.Print AcctType
Debug.Print AcctNum
i = i + 1
If i = 50 Then Exit Do


End Sub
-----

But is splits the first column into two. Here is the debug window
output:

sImportTesting
2
Jan 2007
01
I

Any ideas on how to get this to work properly?

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.- Hide quoted text -

- Show quoted text -