Reading Old Access / CSV data with line breaks?

[scottwhittaker]

Hi

I have an old MS Access property database which I have opened in Excel just to view it. There is approx 500 rows.

Basically most of the columns are filled in with normal simple data such as a basic address or a post code or a price. However the description column is a full on description with line breaks etc. In Excel these show up as boxes with a question mark in them.

 

Is there a good way of getting this data into a mySQL database? Without the description column its simple and ideally I'd rather these long descriptions were stored in text files or html files and pulled in when required. I attempted this but when I exported it to a csv file the line breaks seem to mess it all up.

 

Is there a way of doing this or is it a no hoper?

 

Cheers

Scott

[JD*]

You could write a PHP script to read in the csv file (fopen), then make a SQL insert for each row. Echo everything out to the page first, and check to see what the line breaks are like and see if you can use nl2br to convert them, then put it into your database (one row at a time) and see what happens.

 

If you echo out your sql insert statement first, you can copy/paste it into a MySQL frontend tool and see if it executes 

[scottwhittaker]

So far I have done the following...

1) Pulled out the problematic description column into a new excel spreadsheet.

2) Find and Replace all (") speech marks with something similar or just with blanks (reason for this will become clear later)

3) Exported it as a csv file.

4) It seems that when cells with new lines within them are exported to csv they are enclosed within ("...") speech marks which means I can now read the file into a string variable and "explode" the string into an array at every speech mark. Theoretically each odd numbered element of the array should contain the description data of a row from the original database.

 

This seems to work but doesnt keep the new lines intact and simply outputs everything into one long paragraph. Not very pretty at the minute but getting closer.

 

Scott