Exporting from Access to a CSV file

[Aish1108]

How do I export a access table with a large Memo field to a CSV file?

Do I replace all qoutes with double quotes? WHat about comma's?

What is the syntax to replace a newline. How are newlines stored in Access with a chr(13) or something else?

What do i replace the chr(13) with?

Replace([field1],chr(13),"""")

I have been trying to find an answer for this all week.

 

[AlexCuse]

Access will do the export for you automatically if you just right click on the table and choose 'export'. Why are you asking this in the SQL Server forum though? If the aim of this exercise is to get your table into SQL server there are several ways you can do it that are much easier.

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Aish1108]

I'm writing this in the SQL Server forum because my website is hosted by GoDaddy which does not allow remote access to their SQL server.

(From what I understand from other posts I have seen they only allow access from their IP address)

The only way to to access the server is throgh their control panel. To move the data I must import it through a CSV file into a table.(One table at a time)

My website is completely dynamic with all my data stored in a Access database. I need to move all my data and tables to a MS SQL database as I now have over 500 visitors a day and would like to increase that number.

The problem is that one of my tables has a memo field. Which is filled with quotation marks, commas, newline charachters and white space.

I can't even export it properly into Excel as a CSV because of this. I know the soloution has something to do with placing double quotes around all of these. I'm just not sure of the syntax?

 

[AlexCuse]

Can you use a .dbf, .txt or normal excel sheet to import?

If not, I guess you could create a key for your memo field. Replace all the characters that cause problems with CSV with seldom used characters. In access I believe it would be something like this:

select Replace([MemoField],"'","^") from tblName

This example is to replace a single quote with a carat sign.

You will probably have to nest several of these together to replace everything.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.