CSV question

Dondrei

Diabloii.Net Member
CSV question

At work we regularly work with large datasets which we sometimes have to export in CSV format for one reason or another. I'd like to be able to run a simple check of the number of records (ie rows, not entries) to make sure it exported correctly, now if the file is small enough I can open it in Excel and check that way but frequently it isn't.

My crappy workaround is to open it in Word and use word count to get the number of lines (I told you it was crappy), but I'd love to know if there was a better way. I mean, for one thing I have to set the page margins to zero and put it landscape to make sure all rows only take up one line... plus I have to wait forever while Word calculates the word count... it completely sucks.

Does anyone know of a better way? I can't really use any utilities I need to download since this is a work computer and clearing an app with IT is like pulling teeth.
 

garublador

Diabloii.Net Member
Re: CSV question

You can use the "find" command in DOS with the "/c" option and look for a line feed. I'm not sure how to get the line feed character right in DOS, but I'm sure there's a way.
 

Eritar

Diabloii.Net Member
Re: CSV question

Pop this in a notepad file, save it as a .vbs, and then run it.

Code:
Const ForReading = 1

Dim filePath

filePath = Inputbox("Enter the file path", "Amazing Linecount Utility©")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(filePath, ForReading)

objTextFile.ReadAll
Wscript.Echo "Number of lines: " & objTextFile.Line
Source
 

krischan

Europe Trade Moderator
Re: CSV question

Any text editor worth mentioning displays the number of lines somewhere.

Unix/Linux: wc -l file.csv :tongue:
 

Dondrei

Diabloii.Net Member
Re: CSV question

Thanks, I'll give those things a shot when I'm at work.

Any text editor worth mentioning displays the number of lines somewhere.

Unix/Linux: wc -l file.csv :tongue:
Notepad/Wordpad aren't worth mentioning.



 

garublador

Diabloii.Net Member
Re: CSV question

Thanks, I'll give those things a shot when I'm at work.
With mine, you can also use the /v modifier (display lines without the given search string) if you can't get it to work with searching for line feeds. You just have to search for a string that you know doesn't exist in any of the lines.

The other method looks pretty easy, though.



 

Dondrei

Diabloii.Net Member
Re: CSV question

Hey thanks Eritar, that vbscript worked like a charm.

Notepad++ is free and it says lines.
you got loads of options here :) pick 1
Yeah, I know there are free utilities but getting anything past IT security is a pain in the backside. In fact they're even less likely to let it through if it's freeware.



 

5Ws

Banned
Re: CSV question

At work we regularly work with large datasets which we sometimes have to export in CSV format for one reason or another. I'd like to be able to run a simple check of the number of records (ie rows, not entries) to make sure it exported correctly, now if the file is small enough I can open it in Excel and check that way but frequently it isn't.

My crappy workaround is to open it in Word and use word count to get the number of lines (I told you it was crappy), but I'd love to know if there was a better way. I mean, for one thing I have to set the page margins to zero and put it landscape to make sure all rows only take up one line... plus I have to wait forever while Word calculates the word count... it completely sucks.

Does anyone know of a better way? I can't really use any utilities I need to download since this is a work computer and clearing an app with IT is like pulling teeth.
What environment are you working in? If it's Linux, just use vi or emacs.
How large is your large dataset? I frequently work with sets that have millions of observations and upwards of 500 variables, and I can tell you, number of rows is the least of your worries in exporting/importing.
What format is the original dataset in? And what software do you use to do the CSV conversion?



 

krischan

Europe Trade Moderator
Re: CSV question

Nitpicking: Ctrl-G shows the number of lines without going to the bottom, but that will do the job even better :azn:
 

Dondrei

Diabloii.Net Member
Re: CSV question

What environment are you working in? If it's Linux, just use vi or emacs.
How large is your large dataset? I frequently work with sets that have millions of observations and upwards of 500 variables, and I can tell you, number of rows is the least of your worries in exporting/importing.
What format is the original dataset in? And what software do you use to do the CSV conversion?
The situation is kind of complicated, basically most things are on Win 2000 but we use SAS EG on a dedicated SAS server, which is Unix. I have Putty/telnet but my shell access is restricted (I should really talk to the guys in Infrastructure at some point) so basically the most powerful command at my disposal is ls. And I can't access any directories through that.

We have several products with over a million accounts each, never mind daily transactions for each account and so on, and we have a ****load of tables with ~500 variables (and yet they give me a 14Gb server quota, LO-****ing-L). But since all the load happens on the server rather than on our (ancient) local machines size really isn't the problem, by far our biggest problem is the interface between Unix and Windows environments. More specifically lack thereof (there are actually plenty of ways to fix that but the business DELIBERATELY does not implement them for a variety of irritating reasons).



 

5Ws

Banned
Re: CSV question

The situation is kind of complicated, basically most things are on Win 2000 but we use SAS EG on a dedicated SAS server, which is Unix. I have Putty/telnet but my shell access is restricted (I should really talk to the guys in Infrastructure at some point) so basically the most powerful command at my disposal is ls. And I can't access any directories through that.

We have several products with over a million accounts each, never mind daily transactions for each account and so on, and we have a ****load of tables with ~500 variables (and yet they give me a 14Gb server quota, LO-****ing-L). But since all the load happens on the server rather than on our (ancient) local machines size really isn't the problem, by far our biggest problem is the interface between Unix and Windows environments. More specifically lack thereof (there are actually plenty of ways to fix that but the business DELIBERATELY does not implement them for a variety of irritating reasons).
Ooo, that's tough...how do you get anything done with these limitations?
I use SAS as well, through either the Linux server (PuTTY as well), or my own machine. Although, there's no access or size restrictions.
We also have this thing called WebDrive. We use it to make a virtual drive of the server in Windows. Considering that I'm not nuts about Linux, it's pretty useful for me.



 

Dondrei

Diabloii.Net Member
Re: CSV question

I can do most things I need to just through Windows' interface with the drive, I mean all I really need most of the time is standard stuff like copying and pasting files, it's just those one or two really irritating little quirks that arise because Windows says tomahto and Unix says tomayto that cause really amazingly big problems.

The guy who used to be my supervisor when I first joined (we've both since moved teams) is running a centralised automated reporting system built on SAS, all of which works amazingly well except for the export stage... because our business is a million years behind the times and still using Excel for its reports (don't get me started) and SAS's ability to export to Excel is extremely hampered because [long boring explanation of office politics goes here], anyway the upshot of it all is that he's using a Base SAS licence on his machine (even though everyone has shifted to EG now, God knows how much it costs the business to have just one Base licence for just one PC, Jesus) for no other purpose than to run exports, because Base can get around the barriers in place for EG (thereby making the whole logic of isolating EG invalid, but what the hey).

Anyway, I could get into war stories but I've probably bored everyone enough for tonight.
 
Top