Exporting table to CSV file (1 Viewer)

[popen_73]

I am having probs in exporting a table as a CSV file.
The file will be uploaded to a National Database. I am having problems that the National Database identifies when the user attempts the upload.
The problem is that one of the fields in the table is a memo field. When the user who enters the data hits return to start a new line in the memo field, the csv file is also picking this up, continuing the data on another line. This throws the whole process out of synch and I have not come across this beforehand.
Does anybody know of a way around this?

Many thanks in advance,

-NEIL

 

[databasedonr]

This should work; I've done it mulitple times. The question to ask is, what is the database at National?

When the data is exported, the memo field data should be enclosed in double quotes, like "Memo data". If this is being imported back to an Access database, I am very curious as to why it doesn't work.

If you are importing to a SQL database, like Oracle or MSSQL Server, you need to make sure that you are using quoted identifiers on that database. This way, the "hard return" information is enclosed in the double quotes, and should import correctly.

If this isn't the case, is there more information you can provide? On an Access 2000 database, I successfully exported and imported today.

Good luck.

 

[popen_73]

Hi and thankd for the reply.
After looking around various sites, the problem is the dreaded 'carriage return', which creates a new line on the csv file when the data is exported.
I am not sure what the Db is at the other end, most likely SQL.
I have everything in place except for the export.
This carriage return prob is really driving me nuts.

 

[databasedonr]

Yes, the dreaded carriage return, but it shouldn't matter as long as your text is encapsulated in double quotes, and the receiving database is aware of that. Even if the data is manipulated in the csv file (which I assume it is not), as long as it remains in the double quotes, it should be okay.

On the receiving end, if it is MS SQL, you can use the Data Transformation Services to import the data, and manipulate it that way.

I don't think there is much more you can do, if the problem is on import. When you export the data, make sure you choose to export to a text file, choose comma delimited, and name the file with the csv extension. You can then look at the file with notepad, and see that the extra line is there, but it is within the double quotes.

Importing the data back into a database recognizes that (at least with Access) and sets up the memo field accordingly. With a SQL database at National, they just need to be aware that the data is coming in this format, and manage it.

At least, that's my 2 pence worth! Can work, does work, should work!?

 

[jegenes]

I have a quick question with regards to this. How do you export a comma as text? In other words, how would you export a field that looks like this:

13245

and then attach a comma to it so it comes out in the text file looking like this:

13245,

I've tried appending a comma with: & "," but when it exports it it looks like this:

"13245,"

or this:

13245 ","

I need to get rid of those quotes. Thanks.

John

 

[Pat Hartman]

If you use the transferText Method, the default is numeric fields are unquoted. So,
12345,"text1",245.456,"text2"