Question about CSV files

[shdawson]

Hi,


I have some data from an XML based spreadsheet. It stinks. To wit, it is poorly structured XML. Paste, paste values, and everything else I have tried....it comes in as garbage. It is physically in the cells, but formulas and SQL queries do not see the garbage that occupies the cells. Looks right, reads right, but "ain't" right.

The only way I can get real data is to take the XML based spreadsheet, save as CSV, then open the CSV and paste into the target spreadsheet.

That has been going pretty well, until today.

IT updated my box. Not a fun day, to say the least. Now, in order to get the data into the target spreadsheet, I must now paste as Unicode. Livable.

However, another data source I use is a spreadsheet export from MS Project. Save the project plan as a spreadsheet via native project field mapping. I have been exporting that to CSV as well, for consistency. I now have to paste the CSV into the target spreadsheet as paste-special, Microsoft Object, and then select CSV. That does not recognize date stuff, but the previous data source (XML spreadsheet to CSV) does work as date.

Anyway, a CSV from an XML file is not the same as a CSV file from an XLS from a project file. There is something happening behind the scenes from the original data source to the CSV, where Excel now gets confused. I thought it was isolated to just date data type today while working on this. But, there is also a field that has text and numbers. Once I paste in the information, I then only see the numbers when hitting it with formulas and queries. The fields with the text strings do not show up in formulas and queries. Again, it looks right, reads right, but "ain't" right.

So, the pasting is not bringing consistent results. My thought was CSV is as bare bones as I could hope for. Is there a more preferred bare bones option to go with, in order to get rid of XML and MS Project junk, get pure data, and put that into a spreadsheet....and let the spreadsheet do the data type?


Thanks,
S

 

[RSXchin]

What isn't right about it? Is it a simple format problem? i.e. numbers stored as text or something like that?

 

[shdawson]

Hi,


Thanks for chiming in on this.

Not right, in that the date fields are not evaluated 1 to another. say a1 and a2 are dates. a2-a1 gives the number of days between those dates. The formula is not working after getting the data in as specified above.

The field with the text and numbers, only the numbers are being seen. all that is being seen is blanks. I proved this through an SQL query of select distinct as well. i select these fields a part of a select statement for a query, and nothing is coming in but the cells with numbers now.


Thanks,
S

 

[xenou]

XML is pretty bare bones already. Try importing the XML file rather than copy/pasting it. If there's mixed text and numbers in the same field that is by definition a text field - not much to do about it except work with it the way it is (conversions, transformations, tweak formulas, etc.).