Excel Help.

Create a Post
J

Jay H

PFG, Picture Framing God
Joined
Dec 8, 2003
Posts
9,908
Loc
KY
A supplier sent out a huge list like this.

"1003","2" ASH/GOLDEN OAK "HEARTLAND"",2,2.7,4.2,""
"1003CS","CORNER SAMPLE",0,1,0,""
"10101","",1.5,3.3,5.35,""
"10114","1" ANT SILVER/RIBBED "IMPRESSARIO"",1,2.75,4.15,""
"10114CS","CORNER SAMPLE",0,1,0,""

Clearly this example is 5 seperate lines. I can't get excel (or open office that I use) to see it as 5 lines. When I open it as CSV and the text delimiter is " then it opens that string as 2 lines. I think they have some poorly placed quotes that foul it all up. Can anybody help me open a messed up CSV data base like this up correctly as a spead sheet?
 
Hi Jay,

Take a copy of the file, and use a text editor to global replace all the quote marks with nothing.

Then try importing the modified text file into Excel using CSV conversion, with commas as text field delimiters, and carriage return as record delimiter.

Note: you may need to fiddle around to check that each line contains the same number of commas (i.e. the same number of fields per record).
 
I think moglet's suggestion is probably the simplest. My experience importing CSV files in excel is limited, but I do remember you can configure the delimiter if you use the import function. You may need to set a comma as a delimiter.

The odd thing is that the second column sometimes has an odd number of double quotes. This is rather non-standard and is likely the result of their system being strange. Depending on how excel likes to import data, you a simple Perl script could clean up the data for import.
 
I believe the reason for the quote marks being at the beginning and end of some of the fields is that there are quote marks within some of the fields, i.e. this is what is supposed to show up:
2" ASH/GOLDEN OAK "HEARTLAND"

If you don't care about the size showing that it's inches, and the line name (I'm supposing) being in quotes, do as already suggested and just search and replace all the "s with nothing using Notepad before opening in Excel.

If you want the description to remain as given it gets a bit more complicated:
1. Make it a .txt file as opposed to .csv
2. Open from Excel so you get the Text Import Wizard.
3. Use "Delimited" type and click Next
4. Select "Comma" as the Delimiter Type
5. Select a Text Qualifier of None
6. Finish

You will now have what you originally got - quote marks and all - in spreadsheet format. Now you need to get rid of the extra quote marks:
1. Select Column A and type Ctrl+F
2. Go to the Replace tab and put " in the Find and leave the Replace With blank.
3. Click Replace All
4. In Column G type the following text: =MID(B1,2,200)
note: this assumes there are less than 202 characters in the description field...
5. In Column H type the following text: =LEFT(G2,LEN(G2)-1)
6. Copy Column H
7. Paste Special > Values into Column B
8. Delete Columns G & H

Note - there is what appears to be an extra, empty Column F. Since you only provided an example of five lines there could well be data in it somewhere. What to do to get rid of the quotes there would depend on the type of data is in it. Of course, if it's empty, just delete it (after you do the above procedure, or the formulas won't work as written). Or, if it's like Column A you can do the same Find and Replace on that column only.

Good luck!
 
Here's a screenshot of what the spreadsheet looks like before you copy and paste (special) the Description column:
CSV-import.jpg
 
Thanks friends. This was most helpful.

David, do you use excel often? You're the excel master. The list I recieved was created by CMAC. Do you use that? Anyway every single list they send me is slightly different. This was the first with all these " all over the place.
 
Jay, you're most welcome.

No, we don't use CMAC, but am familiar with who/what they are.

I'm a newcomer to Excel, but have used its counterpart - Quattro Pro - for many years, doing exactly this type of thing. Once you figure out what formulas can do you can do almost anything.

Hope it works out for you.
 
This is the kind of list I get from Decor every month.

Àine’s description is probably the easiest, but just in case you like picchurs, …

First start out with a Word Document. In the Preference Menu check “All”

Excel1.jpg


This will allow you to look at the formatting as you go along.

Excel2.jpg


note each line ends with a “Return/Paragraph” character which is what is needed to end a field in a spreadsheet/database.

Pull down the “Edit Menu” and chose “Replace”

Excel3.jpg


Excel4.jpg


in the “Find What“ field, type a parenthesis (“)
in the “Replace With” field, make sure it it empty by either pressing the “Clear” or ‘Delete” button. Then click the “Replace All” Button

You will end up with your Word Text looking something like

Excel5.jpg


Once you’ve gotten rid of all of the parenthesis, recall the “Replace” window, but this time change the commas to tabs.

Excel6.jpg


In the “Find What” field, type in a comma (,)
in the “Replace With” field, type in “^t” i.e. Shift-6-t, then click "Replace All".

Excel7.jpg


you will end up with the Word text looking like this with all the commas replaced with tabs (the little right facing arrows). Save this Word document as Text.

Open this Text document with Excel and the spreadsheet should look something like.

Excel8.jpg


If you’re sure that columns D and E are currency, change the formatting to fixed decimal at two places, and you’re good to go.
 
In Bill's example, when you save as an .xls file, you want to make sure that column A is formatted as text and that numbers are designated as text, if you want it to sort correctly to use in a lookup function.
 
Create a Post
Back
Top