| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 99653 | 2009-05-10 00:10:00 | handling csv files | piva (3796) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 772637 | 2009-05-10 00:10:00 | I have a large flat ( no cr/lf) csv file which I need to separate into 8 field lines. can anyone think of a better way than reading into word and putting the cr/lf manually. I want to be able to process the file in excel . Piva |
piva (3796) | ||
| 772638 | 2009-05-10 01:15:00 | I might be misinterpreting here, but does opening the file in Excel and using text to columns not work? | the_bogan (9949) | ||
| 772639 | 2009-05-10 02:23:00 | CSV is already separated, opening in in Excel should do the job, remember to save as CSV when you are done though or it will add Excel code to the CSV and make it useless. | DeSade (984) | ||
| 772640 | 2009-05-10 02:47:00 | I have tried to load as csv directly into excel and that works but that can only load 250 columns into excel and the file has more than that number of entries. I guess i will have to load into word and do it the hard way! Thanks |
piva (3796) | ||
| 772641 | 2009-05-10 02:48:00 | Or upgrade to 2007, pain aye? | the_bogan (9949) | ||
| 772642 | 2009-05-10 03:13:00 | Piva: Check your PMs :). | Erayd (23) | ||
| 772643 | 2009-05-10 11:52:00 | If it is a fixed width set of fields, then you have more options available in order to add the CR/LF code. I use MS Access with a lot of CSV's. The Import... From Txt / CSV / TSV options provide further control for splitting your fields and records. |
Paul.Cov (425) | ||
| 772644 | 2009-05-11 11:56:00 | You should be able to do the conversion automatically in Word. Just select the text, choose Text to Table, pick comma as the separator & specify the desired number of columns. Select the table, copy & paste into Excel & you're done. Should be fine for a once-off. If you want to do it regularly, you could probably automate via macros, or even do it via a script (see Windows Shell Host). |
MushHead (10626) | ||
| 772645 | 2009-05-11 12:12:00 | I think the problem piva is having is not the conversion from text to columns as such, it's the fact that there are a ton of columns which need to be broken down (I'm guessing here that Erayd has sent him/her a script or something which would do it). So in a nutshell, the problem would be that the file looks like: aaa, bb, cc, ddd, eee, fff, ddd, eee, aaa, eee, aaa, eeee, aaad, ewea, dewa, adesa, esad ... And piva wants it to look like: aaa, bb, cc, ddd, eee, fff, ddd, eee, aaa eee, aaa, eeee, aaad, ewea, dewa, adesa, esad ... |
somebody (208) | ||
| 772646 | 2009-05-11 12:55:00 | ...I'm guessing here that Erayd has sent him/her a script or something which would do it...I offered to do a regex conversion for him/her, but apparantly it's not just a one-off - and my scripts aren't usually very user-friendly for use on Windows, so they still need a solution! | Erayd (23) | ||
| 1 2 | |||||