• 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

    • Nintendo Switch 2 launches, where to buy and a list of games that it may not support by Sayan Sen Nintendo announced the Switch 2 back in early April this year and then followed that up with more details related to performance and hardware features later. The company touted 10x the performance of the Switch. However, on the flip side, the battery suffers, and you also need new microSD Express cards for storage. For those who need a refresher, here are the technical specification details of the Switch 2: Specification Details Dimensions Approx. 166mm x 272mm x 13.9mm (with Joy-Con 2 attached); Maximum thickness from control stick tip to ZL/ZR buttons: 30.7mm Weight Approx. 401g (console only); Approx. 534g (with Joy-Con 2 controllers attached) Screen 7.9-inch capacitive touch LCD; 1920x1080 resolution; HDR10 support; VRR up to 120 Hz CPU/GPU Custom processor made by NVIDIA Storage 256 GB UFS (a portion reserved for system use) Communication Wireless LAN (Wi‑Fi 6), Bluetooth; Wired LAN available in TV mode via dock Video Output Up to 3840x2160 at 60 fps via HDMI in TV mode; Supports 120 fps at lower resolutions; HDR10 enabled Audio Output Linear PCM 5.1 channel via HDMI; Stereo speakers Microphone Built-in monaural microphone with noise cancellation, echo cancellation and auto gain control Buttons POWER and Volume buttons USB Ports 2 USB Type-C ports (bottom port for charging/dock connection; top port for accessories/charging) Audio Jack 3.5mm stereo mini plug (CTIA standard) Game Card Slot Supports both Nintendo Switch 2 and Nintendo Switch game cards Expansion Slot microSD Express card slot (compatible with cards up to 2 TB; other microSD cards can copy screenshots and videos) Sensors Accelerometer, gyroscope, brightness sensor Battery Lithium-ion, 5220 mAh; Approx. 2–6.5 hours lifetime; 3-hour charge time in sleep mode Dock Approx. 115mm x 201mm x 51.2mm; Weight: approx. 383g For those looking to get one, major retailers like Walmart, GameStop, Best Buy, and Target have all confirmed that they will have limited console stock from time to time so you will need to be on alert and check back. Nintendo has also published a full list of games that may not work on the Switch 2: Borderlands 3 Chrono Cross: The Radical Dreamers Edition Crash Bandicoot N-Sane Trilogy Guilty Gear XX Accent Core Plus R KarmaZoo Marvel vs. Capcom Fighting Collection: Arcade Classics Mortal Kombat 1 Overwatch 2 Star Wars: Knights of the Old Republic II: The Sith Lords Star Wars Republic Commando Super Mega Baseball 4 Tombi! Special Edition Tony Hawk's Pro Skater 1+2 Touhou Genso Wanderer Reloaded Ty the Tasmanian Tiger HD Warriors: Abyss However, keep in mind that Nintendo last updated the support list last month on May 27th and the company may still be testing these. So keep an eye on the official list of games on this webpage here on Nintendo's site. Have you managed to pick up the Nintendo Switch 2? Let us know in the comments.
    • Court orders Apple to keep web links in the App Store, eroding its iOS payment monopoly by Fiza Ali Apple has been ordered to continue permitting web links and external payment options in the App Store after its bid to halt court’s ruling was declined today by a higher court. Earlier this year, in April, a federal judge decreed that Apple must allow developers to include web links in their iOS apps, remove restrictions on link formatting, and enable external payment methods without taking a commission on transactions. Apple immediately appealed and sought an injunction to delay implementation of the order while the case progressed. However, the United States Court of Appeals has now refused Apple’s emergency request to stay the district court’s order. In its decision, the panel held that Apple had not demonstrated a sufficient likelihood of success on appeal, nor that it would suffer irreparable harm if the order were enforced. The court also considered potential prejudice to other parties and the public interest, concluding that an immediate suspension was not warranted. This ruling makes it much harder for Apple to overturn the April decision, which came from a lawsuit initiated by Epic Games. Epic first sued Apple’s App Store policies in 2020, claiming that the company’s restrictions harmed competition. While Epic did not prevail on every count, the court did rule that Apple must allow developers to inform users of alternative purchasing options at better prices. Despite that narrow victory, Apple repeatedly failed to conform to the terms from the original 2021 ruling, prompting the judge in April to issue a more detailed order outlining precisely how the App Store must be “opened up”. In response to the April ruling, prominent third-party apps have swiftly implemented web-based purchasing links. Both Spotify and Amazon’s Kindle app now include buttons directing users to purchase subscriptions via their websites, bypassing Apple’s in-app payments. Additionally, Fortnite has made a comeback on iOS after around five years, presenting users with the choice between Apple’s in-app payment system and Epic’s own payment and rewards mechanism. According to Epic CEO Tim Sweeney, there is presently a 60:40 split in usage favouring Apple’s system over Epic’s, though the gap appears to be narrowing. An Apple spokesperson, Olivia Dalton, issued a statement expressing the company’s disappointment: For now, Apple must comply with the existing injunction. Unless the Appeals Court later overturns the ruling, developers can continue to include web payment links, and Apple’s longstanding monopoly over iOS payment processing may continue to erode. The ultimate resolution will depend on the outcome of the ongoing appeals, which could set a significant precedent for how app marketplaces operate in the future. Source: The Verge
    • Reddit posts are all public, no login(therefore no agreeing to contract) to view the content. It's like the equivalent of sitting in a library and writing down notes from a textbook without signing it out and they start suing you for writing notes.
  • Recent Achievements

    • Rookie
      GTRoberts went up a rank
      Rookie
    • First Post
      James courage Tabla earned a badge
      First Post
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
    • Apprentice
      DarkShrunken went up a rank
      Apprentice
    • Dedicated
      CHUNWEI earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      397
    2. 2
      +FloatingFatMan
      177
    3. 3
      snowy owl
      170
    4. 4
      ATLien_0
      167
    5. 5
      Xenon
      134
  • Tell a friend

    Love Neowin? Tell a friend!