Prev: Opening a blank form
Next: References are not working across computers
From: Boon on 1 Apr 2010 14:46
Hello,

I work a lot with importing the csv file into the database. The annoying
problem I have is when there is a comma in a csv file, it screw up the
import.

I am not sure whether I shuld do anything in the Access, or doing in the csv
file itself. Basically, I have another program that generates the csv file.
then I import the csv file into my Access database.

Should I focus on the import in Access, or focus on making sure that csv
file does not contain extra comma.

every suggestion is very welcomed.

thanks,
Boon


From: Douglas J. Steele on 1 Apr 2010 15:03
CSV means "comma separated values". The position of the commas is critical!

You'll have to provide more details about the problem.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Boon" <boonyawat.la-ongthong(a)cnh.com> wrote in message
news:ePd3vuc0KHA.776(a)TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I work a lot with importing the csv file into the database. The annoying
> problem I have is when there is a comma in a csv file, it screw up the
> import.
>
> I am not sure whether I shuld do anything in the Access, or doing in the
> csv file itself. Basically, I have another program that generates the csv
> file. then I import the csv file into my Access database.
>
> Should I focus on the import in Access, or focus on making sure that csv
> file does not contain extra comma.
>
> every suggestion is very welcomed.
>
> thanks,
> Boon
>
>


From: Marshall Barton on 1 Apr 2010 15:51
Boon wrote:
>I work a lot with importing the csv file into the database. The annoying
>problem I have is when there is a comma in a csv file, it screw up the
>import.
>
>I am not sure whether I shuld do anything in the Access, or doing in the csv
>file itself. Basically, I have another program that generates the csv file.
>then I import the csv file into my Access database.
>
>Should I focus on the import in Access, or focus on making sure that csv
>file does not contain extra comma.
>

SInce it may be impossible to tell whether a comma is part
of a field or a field separator, I suggest you do something
in the export process. It may be as simple a replacing the
commas in a field with a different character and then
replacing again using a update query after the import.

--
Marsh
MVP [MS Access]
From: John W. Vinson on 1 Apr 2010 17:05
On Thu, 1 Apr 2010 13:46:47 -0500, "Boon" <boonyawat.la-ongthong(a)cnh.com>
wrote:

>Hello,
>
>I work a lot with importing the csv file into the database. The annoying
>problem I have is when there is a comma in a csv file, it screw up the
>import.
>
>I am not sure whether I shuld do anything in the Access, or doing in the csv
>file itself. Basically, I have another program that generates the csv file.
>then I import the csv file into my Access database.
>
>Should I focus on the import in Access, or focus on making sure that csv
>file does not contain extra comma.
>
>every suggestion is very welcomed.
>
>thanks,
>Boon
>

If the text fields in the csv file are delimited with quotemarks, there
*SHOULD* be no problem: e.g.

"ABC","this, string, contains, commas", 254

will be seen as two text strings and a number.

Of course if your numbers contain commas as thousands separators, or the text
strings aren't delimited, you'll have a problem!

Could you post a sample of the data - or are you the same person who posted
this question last week...?
--

John W. Vinson [MVP]
From: Boon on 6 Apr 2010 12:41
Thanks all. Sorry for late reply...

I will try the quote suggestion and see if I can do something.

Here is the situation I have.

I use Adobe Acrobat to collect the survey data from several people (around
100). When Acrobat export the response file into CSV format, the file
displays incorrectly in Excel. (The problem is extra comma). I think what I
will do next is to figure out when exporting from the Acrobat if I can put a
quote for each field (column).

thanks.

"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:vf2ar5hpt0994vqp0v4dv7m41fscb14a2j(a)4ax.com...
> On Thu, 1 Apr 2010 13:46:47 -0500, "Boon" <boonyawat.la-ongthong(a)cnh.com>
> wrote:
>
>>Hello,
>>
>>I work a lot with importing the csv file into the database. The annoying
>>problem I have is when there is a comma in a csv file, it screw up the
>>import.
>>
>>I am not sure whether I shuld do anything in the Access, or doing in the
>>csv
>>file itself. Basically, I have another program that generates the csv
>>file.
>>then I import the csv file into my Access database.
>>
>>Should I focus on the import in Access, or focus on making sure that csv
>>file does not contain extra comma.
>>
>>every suggestion is very welcomed.
>>
>>thanks,
>>Boon
>>
>
> If the text fields in the csv file are delimited with quotemarks, there
> *SHOULD* be no problem: e.g.
>
> "ABC","this, string, contains, commas", 254
>
> will be seen as two text strings and a number.
>
> Of course if your numbers contain commas as thousands separators, or the
> text
> strings aren't delimited, you'll have a problem!
>
> Could you post a sample of the data - or are you the same person who
> posted
> this question last week...?
> --
>
> John W. Vinson [MVP]


 |  Next  |  Last
Pages: 1 2
Prev: Opening a blank form
Next: References are not working across computers