Using TransferText to import .csv files

[sparker]

Greetings Community!

I have to import hundreds upon hundreds of csv files into Access and the
files have commas randomly placed throught the data in various fields. Upon
importing the csv (Comma Seperated Value) Files, some of the data gets askew
when there is a comma in it. Any ideas on the best solution to import these
files would be greatly apreciated. I am thinking that they will need to be
opened one at a time and have the find/replace executed via Excel where , is
replaced with a Space(1)... Anybody?

Private Sub ImportBOR()

Dim strPath As String
Dim strFile As String
Dim strOldName As String
Dim strNewName As String

strPath = "C:\6-15-05 Business Objects Reports\"

strFile = Dir(strPath & "\*.csv")

Do While strFile <> ""

"I believe that maybe I need code placed here to open each file and perform
a find/replace on all of the commas?"

DoCmd.TransferText acImportDelim, "BOR_ImportSpec", "tblNewBOR", strPath &
strFile

strOldName = strPath & "\" & strFile
strNewName = strPath & "\" & Left(strFile, Len(strFile) - 3) & "sav"
Name strOldName As strNewName
strFile = Dir(strPath & "\*.csv")
Loop

strFile = Dir(strPath & "\*.sav")

Do While strFile <> ""
strOldName = strPath & "\" & strFile
strNewName = strPath & "\" & Left(strFile, Len(strFile) - 3) & "csv"
Name strOldName As strNewName
strFile = Dir(strPath & "\*.sav")
Loop
End Sub

 

[John Nurick]

Hi Sparker,

In standard CSV files, any field whose data includes a comma must be
"delimited" or "qualified" by being enclosed in quote marks, e.g.

123,Smith,34.50
124,"Bob, Carol, Ted & Alice",102.17

Quotes are optional if there is no comma in the field, so in the example
above you could have either
Smith
or
"Smith"
.. Without the delimiting quotes, you get

123,Smith,34.50
124,Bob, Carol, Ted & Alice,102.17

and there is no easy way to tell which commas are field separators and
which are part of the data.

What you need to do is either:
(a) enclose fields that contain (or may contain) commas in quote marks
or
(b) replace commas that separate fields (but not commas in the data)
with another character such as tab - chr(9) - or pipe (|).

To automate this, you have to be able to specify unambiguous rules that
a computer can use to distinguish between field-separator commas and
data commas. If you can do this, we can probably help you convert them
into working code. Otherwise the files may well need to be edited
manually before they can be imported.

 

[sparker]

Ok thanks John... Are you aware that in a nut shell you have basicly echoed
back to me exactly what I just said in my origonal post? LOL Sometimes I am
not good at explaining my thoughts in words for some reason the meaning can
get mingled... I realize that the commas need to be replaced/removed which is
what I had said... And I am unable to locate any code or examples on how to
do this. Which brings us full circle back to the origonal post that I am
asking for any code examples that would show how to: Using VBA code in Access
(possibly manipulate Excel) into opening these .csv files and executing the
find/replace code native to the Microsoft Office Suite so that I can replace
all of the commas with spaces and then the files will import without anything
askew. So in conclusion... Does anybody have any code / examples on how to
perform the afore mentioned task? I do apologize if I have not communicated
my request properly. Thank you for any and all help in advance.

 

[Edwin van Ree]

Hi,

I fully agree with John on this. If you cannot unambiguously determine which
comma is a data seperator or a field seperator, what is the use of changing
the commas into something else?

Cheers, Edwin

 

[sparker]

Thanks Edwin... lol Ummm.... How to put this.... When you open a .csv you
can perform a find/replace on the data therein.... So if there is a way to
open the file with code (which there is...) and then execute the find/replace
function (which I am searching for) then the file can be imported without any
problems. I do not know of any way to make the request for help any more easy
to understand. If there is anybody out there seeing the responses I am
getting and understands what I am seeking and even if you do not have the
solution but you do understand why these other people do not understand where
I am going with this please by all means reword my request so that all can
grasp the meaning... Thank you...

 

[sparker]

Ok people I have written the code. It took me awhile but it is complete and
accurate. If anybody has had a similar task to complete here is the quick an
easy. Also Note: RD I am sorry you feel the need to resort to name calling. I
will pray for you my friend. As for John I do apologize if his feelings have
been hurt in any way as that was not my intention. He had responded to a
couple of my posts without having a solution and I asked him not to that
plain and simple as I would ask of you if you did the same. Take Care & God
Bless ~ Sparker
Oh ... here is the code I wrote enjoy!

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim strPath As String
Dim strFile As String
Dim strOldName As String
Dim strNewName As String
Set oExcel = CreateObject("Excel.Application")
strPath = "C:\CSV_Files\Objects Reports\"
strFile = Dir(strPath & "\*.csv")
Do While strFile <> ""

strOldName = strPath & strFile

Set oBook = oExcel.Workbooks.Open(strOldName)

Cells.Select

Selection.Replace _
What:=",", _
Replacement:=" ", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

oExcel.DisplayAlerts = False
oBook.SaveAs strOldName
oExcel.Quit
oExcel.DisplayAlerts = True

DoCmd.TransferText acImportDelim, "CSV_ImportSpec", "tblNewCSV", strPath
& strFile

 

[John Nurick]

Ok people I have written the code. It took me awhile but it is complete and
accurate.

<chortle>

 

[sparker]

Thank you for all of your help John.