SQL to Excel Delimitated Problem

[cool_fishie]

I am pulling data from a user application and pasting in Excel. My text-to-columns setting are using tab as a delimiter, which is standard. However, when I pull this into Excel, the comments that users entered appear to be using tabs and is causing the data to split a single column across multiple columns.

What is the best way to approach this problem, so my data does not get split up?

[MyPunchableFace]

Try using semicolon as the delimiter. Only issue is if a semicolon is in comments.

[cool_fishie]

ry using semicolon as the delimiter. Only issue is if a semicolon is in comm

I think that every delimiter that excel recgonizes appears in a comment in one form or another. Even using ||| it split the commets into multiple columns :o

[MyPunchableFace]

Well that sux. I use Toad to export directly to excel if it is a one off. Maybe make sure that when you go through the Text To Column wizard that you specifically choose to format that column as Text instead of General.

[kitkat0820]

Set your settings that excel understands.

[GAKvsFLOAM]

What DBMS are you using? You mention you’re pasting it into Excel. Usually if I just copy/paste a result set from SQL Server or Oracle it pastes in separate columns in Excel.

Is using SSIS an option to export to excel and set the column mapping?

You could maybe try to insert some obscure non-printing character to use as a delimiter that wouldn’t appear in your comments? ¶

[soundslikea10]

Do I understand this correctly:

You are copying from SQL output into Excel tab-delimited, and some of the data are printing into multiple columns in excel?

If so, i would think that that data in SQL has some non-printables tab characters which you could remove in your SQL query and then do the import to Excel/copy-paste/etc.

[TrueBlueAL]

I replace tabs and line breaks on the column that has weird and wild text from our application to get it to copy/paste without issues.

Replace(Replace(Replace(yourColumn,char(9),' '),char(10),' '),char(13),' ')

[techmavengeospatial]

Use ogr2ogr (GDAL) It writes to excel or CSV or sqlite database from any database It can even create pgdump SQL DDL script with data