When importing hundreds of csv files into Access via the DoCmd.TransferText I
am getting data that is askew because of random commas in the data itself.
Any code to remove the commas prior to import? ...
--
~ SPARKER ~

See response to your question on the same subject posted a whopping 9
minutes earlier.

On Thu, 16 Jun 2005 16:47:05 -0700, "sparker"
<SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote:

>When importing hundreds of csv files into Access via the DoCmd.TransferText I
>am getting data that is askew because of random commas in the data itself.
>Any code to remove the commas prior to import? ...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Hi John.. Again... With all do respect: Please do not reply to these unless
you actualy have the answer. As now I may not get a response because somebody
may think you have already found/provided a solution as opposed to echoing
back what is in the posting to begin with. I may need to post this again and
as requested please do not respond. Thanks for your help...
--
~ SPARKER ~


"John Nurick" wrote:

> See response to your question on the same subject posted a whopping 9
> minutes earlier.
>
> On Thu, 16 Jun 2005 16:47:05 -0700, "sparker"
> <SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote:
>
> >When importing hundreds of csv files into Access via the DoCmd.TransferText I
> >am getting data that is askew because of random commas in the data itself.
> >Any code to remove the commas prior to import? ...
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>

John tried to help you. He asked that you come up with unambiguous rules to
differentiate between data commas and field separators.

If you don't get a response it won't be because John responded to you but
because you responded back to him like a total jerk. That does not inspire the
unpaid volunteers in this group to really *want* to help you.

Time for an attitude check, sparkie.


On Fri, 17 Jun 2005 06:38:11 -0700, "sparker"
<SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote:

>Hi John.. Again... With all do respect: Please do not reply to these unless
>you actualy have the answer. As now I may not get a response because somebody
>may think you have already found/provided a solution as opposed to echoing
>back what is in the posting to begin with. I may need to post this again and
>as requested please do not respond. Thanks for your help...

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
--
~ SPARKER ~


"RD" wrote:

> John tried to help you. He asked that you come up with unambiguous rules to
> differentiate between data commas and field separators.
>
> If you don't get a response it won't be because John responded to you but
> because you responded back to him like a total jerk. That does not inspire the
> unpaid volunteers in this group to really *want* to help you.
>
> Time for an attitude check, sparkie.
>
>
> On Fri, 17 Jun 2005 06:38:11 -0700, "sparker"
> <SpamFreePlease@(E-Mail Removed)@SpamFreePlease> wrote:
>
> >Hi John.. Again... With all do respect: Please do not reply to these unless
> >you actualy have the answer. As now I may not get a response because somebody
> >may think you have already found/provided a solution as opposed to echoing
> >back what is in the posting to begin with. I may need to post this again and
> >as requested please do not respond. Thanks for your help...
>
>