• 0

Extracting select data from CSV file (one column)


Question

Have a CSV file w/ about 15 columns - when opened in spread sheet (also have couple of "CSV editor" prgms).

Want to take 1 of the columns (by what ever method), & get it into a text config file in another prgm.

When you look at any CSV file in a text editor, there are NO spaces after each comma & before next data (datum).

The syntax for the prgm (text file) I'm trying to enter the data into, shows no spaces to be used between each comma & next data. Just a command then data list, w/ each piece of data separated by commas, but no spaces. It appears when I enter the data (including spaces) in the other prgm's text file, it does NOT use it correctly. I'm * assuming * it's because of the spaces - but I'm no expert on this.

Everything I've tried w/ Open Office, Excell (older ver), the CVS editors, Notepad ++, always winds up putting a space after each comma - before next piece of data.

Even when I delete everything from the orig CVS file except the column I want, once opened in the spread sheets, then save it in .cvs format, then reopen it in something other than a spreadsheet (text editor, etc.), it either has no commas at all, or put spaces between data. In short, the spreadsheets aren't saving the edited files in the EXACT same CVS format (w/ no spaces) as the original CVS file.

Any ideas how to do it the way (I think) I need to do it, w/o introducing spaces? Thanks.

6 answers to this question

Recommended Posts

  • 0

You will need to craft a Regular Expression to match the data you need and to insert it into the desitnation file.

I haven't read this page, but it seems like it might be a good explaination on how to use RegEx in Notepad++

http://markantoniou.blogspot.com/2008/06/notepad-how-to-use-regular-expressions.html

  • 0

Thanks Frazell, took a quick look at the page linked. (It has links to more detailed pages on using regular expressions & examples).

Caught a bit about using "search & replace" in Notepad ++. It was talking about adding spaces, so figured might work in reverse - it did. For this, I didn't have to use a Regular Expression (which I know nothing about). Used Notepad++ "find & replace" function, to find all blank spaces followed by comma, & replace w/ just a comma. Worked fine.

Depending on which spreadsheet I copy one column into Notepad ++, each line in the data copied in to Notepad ++ may / may not have an actual comma (& space) before each value.

First had to use the Edit>Column Editor function to add a comma in front of each value (there was one value on each line, in column form). Put the cursor at the place where want to enter the comma (or what ever want to add).

But, as said, after that, when use "unwrap" in Notepad ++, there's a space between every comma & next value, which I had to remove.

Not sure why the spreadsheets aren't smart enough to (or don't) save BACK to a CSV format & eliminate spaces between commas & values - the same way orig CSV file was before the spread sheet opened it. Seems like would be very common use to open & edit a CSV file in a spread sheet, then save back to CSV format (without spaces) - to open / use in other prgms, instead of going through extra steps of eliminating the spaces. Maybe not - maybe most uses for edited / saved CSV files prefer spaces between separators? Very little experience w/ them.

Prgms like Excel, OO Calc do warn (to the effect): "this file (may?) contain content or formatting that can't be saved in text .CSV format..."

Odd that they just opened a CSV file that contained no spaces between separators, but when saving it back to .CSV, they put spaces between all separators??

  • 0

Interesting, jocaaa. I tried older ver of Excel (office 2000), but newest Open Office Calc. May well be operator error (ignorance), but saw nothing in options how to save the file, once edited in spread sheet(s), back to CSV file, whether to include / exclude spaces (or anything else).

When you opened an original CSV file (which assume contained no spaces) in Excel '07, did you do any editing before saving back to CSV (or maybe trying saving w/o doing editing)?

Maybe there are options / settings in Excell that specify about properties for saving in CSV format, that exclude spaces?

In your example, assuming when you opened it in Excel, it showed 3 cols & 2 rows in spread sheet. Then when saved back to CSV format file, & you looked at that saved file in a text editor, had no spaces?

  • 0

There were no editing at all, and yes, I checked it in text editor and hex editor - no (unwanted) spaces as you describe. I tried several scenarios - first to open an existing .csv file and export (save as) it to another .csv file, and to enter values manually in Excel and export them to .csv - no spaces in either case. In your case double check if you have string values in Excell cells without space characters.

  • 0

I can't find any thing about "string values w/o space characters" in options or help files of either Excell '00, or latest open office Calc.

But, think I see what you mean. The key part of my OP you overlooked was,

  Quote
Want to take 1 of the columns (by what ever method), & get it into a text config file in another prgm.

Yes, if I edit it down to 2 cols, then export to CSV, each value * in a row * has no spaces between commas / values.

But if save only ONE column to a CSV file, then try to manipulate it in text editor (like Notepad ++), & unwrap the rows, so it appears like a CSV file, THEN all values have a space between them.

When I open that saved CSV file w/ only 1 col left, in most editors, it initially shows up as 1 col, w/ 1 value in each row (go figure). But, there are obviously hidden spaces after each value, because when unwrap the col, it does separate each value w/ comma, but also has a space.

I'm assuming this is part of the formatting of a CSV file to specify when to make a new row (for multi row files). That was the problem I had - dealing w/ using only 1 col AND getting rid of the spaces between values.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Yeah, it seemed like a bunch of 2D decal assets to me, so I didn't think it would be a huge issue to replace them. Assuming, of course, that Sony didn't just decide to settle and pay the artist instead. Do we know if that happened?
    • No worries, I wasn't pointing to your post specifically at all. I was talking in general and building upon your idea.
    • This 2TB NVMe Gen4 SSD is priced just $94 and you also get a free 64GB UHS-I memory card by Sayan Sen A few days ago, we reported on Team Group's T-FORCE G50 4TB NVMe SSD that was up for grabs at just $200 thanks to a promo coupon. Sadly, that deal has expired although you can still WD's SN8100 (Gen5) and SN7100 (Gen4) offerings as they are still running the discount. If you don't have the budget for those or are shopping for lower capacity drives then Crucial's T500 Gen4 drive discount is still live, and you can get them for just $125. And while the G50 4TB deal has expired, Team Group is now offering its 2TB model at its lowest ever price and you also get a free Micro SD card with it. The Team Group G50 is also a TLC (triple level cell) NAND flash SSD, and thus the endurance on the T-FORCE SSD is quite good, as it is rated for 1300 TBW (terabytes written) for the 2TB variant. Its MTBF, or Mean Time Between Failure, is claimed at 3,000,000 hours. The operating temperature is 0~70 C. The G50 does not have a dedicated DRAM cache (only the G50 Pro SKUs have it), but since it is based on NVMe version 1.4 which supports HMB (host memory buffer) technology; thus, the drive can use system memory for caching. In terms of performance, Team Group promises sequential read and write speeds of up to 5000 MB/s and 4500 MB/s, respectively. However, the firm does not disclose random throughput metrics. Get the Team Group G50 at the link below (deal is said to be ending in less than 10 hours): Team Group T-FORCE G50 SSD (TM8FFE002T0C129) + Team Group 64GB Elite microSDXC UHS-I U3, V30, A1, Micro SD with SD Adapter, to 100MB/s (TEAUSDX64GIV30A103): $105.99 + $12 off with promo code SSETA665 (Shipped and Sold by Newegg US) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • I know what you're getting at, but Microsoft themselves have said that the Xbox Ally specifically disables a bunch of stuff from Windows to improve performance and save 2GB of memory. And that special game mode is coming to Windows 11 next year.
  • Recent Achievements

    • First Post
      Ian_ earned a badge
      First Post
    • Explorer
      JaviAl went up a rank
      Explorer
    • Reacting Well
      Cole Multipass earned a badge
      Reacting Well
    • Reacting Well
      JLP earned a badge
      Reacting Well
    • Week One Done
      Rhydderch earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      646
    2. 2
      ATLien_0
      269
    3. 3
      Michael Scrip
      218
    4. 4
      +FloatingFatMan
      184
    5. 5
      Steven P.
      146
  • Tell a friend

    Love Neowin? Tell a friend!