Access -> Excel (CSV), linefeed & square boxes - Tilted Forum Project Discussion Community

John Henry · 05-06-2004, 09:01 AM

I need to move data from an access database to an excel spreadsheet, but at the moment, all the linefeeds are coming out as little square boxes. I tried using a bit of vb code from the msdn site, that is supposed to solve the reverse problem (ie. excel->Access) and just swapping the find and replace fields, but this did nothing.

The code is written in such a way that if each field wasn't being fed through this function, nothing would come up on the spreadsheet at all, so the only answer I can think of is that I'm not searching with the correct ASCII code. Anyone know what it's likely to be? Or have any ideas how I can find out? Or can you think of a way round it? Anything, really.

Cheers

JH

Polyphobic · 05-06-2004, 10:55 AM

I have very little experience with access. My certification is with oracle 8i. Moving data in and of oracle is pretty straightforward.

With access I believe I used the export function to move data around. Once, I opened the table and copied it into the clipboard and then pasted it into excel.

Carriage return = ascii code 13
linefeed = ascii code 10

hrdwareguy · 05-06-2004, 11:11 AM

Just open the Access Database with Excel. It will run through an import wizard and bang, you're done.

Polyphobic · 05-06-2004, 11:18 AM

What version of office is that feature in?
My good old poor ass company is still using 97 and it isn't there.

hrdwareguy · 05-06-2004, 01:41 PM

I know it's in 2000 and XP. Thought it was in 97 as well.

When you go to Open from within Excel, you should be able to change the file type to MDB. Then open the MDB file and it will ask which table you want to import. Select the appropriate table and away you go.

John Henry · 05-07-2004, 01:11 AM

Got it all working now. Thanks guys. I couldn't find the mdb filetype to save it as (wierd cos there are several other database filetypes in there) so I randomly tried saving in XML and voila! It works.

Polyphobic · 05-07-2004, 05:43 AM

It's not in 97. Just a little info. Would be nice if it was. Would have saved my some effort on a project I was working on a month ago.

05-06-2004, 09:01 AM	#1 (permalink)
John Henry Addict Location: Grey Britain	Access -> Excel (CSV), linefeed & square boxes I need to move data from an access database to an excel spreadsheet, but at the moment, all the linefeeds are coming out as little square boxes. I tried using a bit of vb code from the msdn site, that is supposed to solve the reverse problem (ie. excel->Access) and just swapping the find and replace fields, but this did nothing. The code is written in such a way that if each field wasn't being fed through this function, nothing would come up on the spreadsheet at all, so the only answer I can think of is that I'm not searching with the correct ASCII code. Anyone know what it's likely to be? Or have any ideas how I can find out? Or can you think of a way round it? Anything, really. Cheers JH __________________ "No one was behaving from very Buddhist motives. Then, thought Pigsy, he was hardly a Buddha, nor was he a monkey. Presently, he was a pig spirit changed into a little girl pretending to be a little boy to be offered to a water monster. It was all very simple to a pig spirit."

05-06-2004, 10:55 AM	#2 (permalink)
Polyphobic Runt Location: Denver	I have very little experience with access. My certification is with oracle 8i. Moving data in and of oracle is pretty straightforward. With access I believe I used the export function to move data around. Once, I opened the table and copied it into the clipboard and then pasted it into excel. Carriage return = ascii code 13 linefeed = ascii code 10 __________________ <--The great infidel-->

05-06-2004, 11:11 AM	#3 (permalink)
hrdwareguy "Officer, I was in fear for my life" Location: Oklahoma City	Just open the Access Database with Excel. It will run through an import wizard and bang, you're done. __________________ Gun Control is hitting what you aim at Aim for the TFP, Donate Today

05-06-2004, 11:18 AM	#4 (permalink)
Polyphobic Runt Location: Denver	What version of office is that feature in? My good old poor ass company is still using 97 and it isn't there. __________________ <--The great infidel-->

05-06-2004, 01:41 PM	#5 (permalink)
hrdwareguy "Officer, I was in fear for my life" Location: Oklahoma City	I know it's in 2000 and XP. Thought it was in 97 as well. When you go to Open from within Excel, you should be able to change the file type to MDB. Then open the MDB file and it will ask which table you want to import. Select the appropriate table and away you go. __________________ Gun Control is hitting what you aim at Aim for the TFP, Donate Today

05-07-2004, 01:11 AM	#6 (permalink)
John Henry Addict Location: Grey Britain	Got it all working now. Thanks guys. I couldn't find the mdb filetype to save it as (wierd cos there are several other database filetypes in there) so I randomly tried saving in XML and voila! It works. __________________ "No one was behaving from very Buddhist motives. Then, thought Pigsy, he was hardly a Buddha, nor was he a monkey. Presently, he was a pig spirit changed into a little girl pretending to be a little boy to be offered to a water monster. It was all very simple to a pig spirit."

05-07-2004, 05:43 AM	#7 (permalink)
Polyphobic Runt Location: Denver	It's not in 97. Just a little info. Would be nice if it was. Would have saved my some effort on a project I was working on a month ago. __________________ <--The great infidel-->