Need help creating a script 4 a CSV file

[Jaybird180]

I have some historical data that I need to input into a paid application that will not accept .CSV; it has its own proprietary format (irrelevant for this discussion???). It's paid software and I'm 3 days into their 60 day full money-back trial, so need to act quickly. The original data is now in .CSV format and I would really like to utilize the data, but won't be totally disappointed without it. I will likely continue using it after the 60-days, even if the historical data is unusable as the application thus far appears to do everything else that I need it to do. That said, I found a little paid utility that will (allegedly) allow me to transform the original CSV to this proprietary format, of which I have a few doubts, but I took a look at the trial version anyway.

The trial version of this utility has a limitation of only transforming 100 lines of data, but will do an 'unlimited number of files', which theoretically should be enough for Proof Of Concept (POC), after which I will pay for the utility. I plan to use the utility once then shelf it, and if it works will consider it money well-spent.

The original CSV files have 1 header row and a few thousand lines of data, which is why doing [copy/paste, copy/paste > save] manually is not really practical. Is someone knowledgeable enough about scripting something that will copy the header row and the 1st 99 rows to 'FILE1.csv' then copy header row and rows 100-199 to 'FILE2', then header row and rows 200-299 to 'FILE3'...(ad infinitum).

If this POC works, I can test 'large file #1' with my application, and if functional will then pay for the utility, discard the script, and then convert 'large file #2', etc. without needing the script further as then the 100 line limitation will be removed.

I'll offer to pay you for the script for helping with my utility pre-purchase test.

Thank you.

 

[wildwobby]

It sounds like it could easily be done by writing a small script and not using the utility whatsoever. What is the format of the proprietary data you need to convert to?

 

[poadeleted21]

This will take me 15 seconds, if I take a 10 second coffee break. So I'll charge for 5 seconds @$100/hr. That's about 14 cents, with interest, that comes to a beer if you ever see me at a fly in or something.

If the file is small enough to send via gmail. I can do it now. Otherwise I'll have to give you ftp credentials to my server, and I'll do it in about 3 hours from now.

 

[Jaybird180]

Rusty - the data is of a Sensitive nature, so there will be no FTP or email. I will only do this on my local machine. The utility is $37, so I may as well pop for it and dispense with the test, right? (sarcasm)

Wildwobby - Well, I guess I asked for it, and it wouldn't be PoA if someone didn't ask; problem is that I think that may be legally murky waters as the final format is proprietary

 

[poadeleted21]

Rusty - the data is of a Sensitive nature, so there will be no FTP or email. I will only do this on my local machine. The utility is $37, so I may as well pop for it and dispense with the test, right? (sarcasm)

Wildwobby - Well, I guess I asked for it, and it wouldn't be PoA if someone didn't ask; problem is that I think that may be legally murky waters as the final format is proprietary

It's one thing to write software to process a file you've never seen before (What I'm doing right now actually)

But developing a piece of software that processes an unseen file on an unknown operating system with unknown tools is beyond my pay grade.

If you're on Linux/have cygwin installed/possibly OS X. I can give it to you in probably one or two lines of shell commands.

 

[Jaybird180]

It's one thing to write software to process a file you've never seen before (What I'm doing right now actually)

But developing a piece of software that processes an unseen file on an unknown operating system with unknown tools is beyond my pay grade.

If you're on Linux/have cygwin installed/possibly OS X. I can give it to you in probably one or two lines of shell commands.

Hmmm.....I thought .CSV was pretty universal, that all OSes can understand them, but now I think I understand what you mean.

The subject OS is Windows. The output format after processing with the script should also be .CSV and saved with Windows (XP).

 

[poadeleted21]

Hmmm.....I thought .CSV was pretty universal, that all OSes can understand them, but now I think I understand what you mean.

The subject OS is Windows. The output format after processing with the script should also be .CSV and saved with Windows (XP).

I wouldn't know where to start on a base XP system.

I'd probably install Cygwin.

If you install cygwin, I'll write up the command for you.

CSV doesn't matter. I watched a guy write a program to process a CSV the other day that had to be totally reworked because he attempted to load the entire file into memory instead of processing it in segments.

 

[Jaybird180]

I wouldn't know where to start on a base XP system.

I'd probably install Cygwin.

If you install cygwin, I'll write up the command for you.

CSV doesn't matter. I watched a guy write a program to process a CSV the other day that had to be totally reworked because he attempted to load the entire file into memory instead of processing it in segments.

I'll see if I can do that...or...use my Ubuntu machine for it (don't use it often enough for proficiency).

I don't have the data in front of me right now, but I've reviewed it in Excel and it has about 6 or 8 rows of data across. It's a simple spreadsheet, no calculations, just data.

And in case anyone's wondering, I have well-founded reason to believe the maker of the Utility secured the permission of the Application creator before offering the Utility for sale, but they don't actively advertise its existence.

 

[wildwobby]

Rusty - the data is of a Sensitive nature, so there will be no FTP or email. I will only do this on my local machine. The utility is $37, so I may as well pop for it and dispense with the test, right? (sarcasm)

Wildwobby - Well, I guess I asked for it, and it wouldn't be PoA if someone didn't ask; problem is that I think that may be legally murky waters as the final format is proprietary

Your the one who asked in the first place. I was just trying to help out... LOL.

Not sure what you want then .

How is writing a custom script to convert to a "proprietary" format any less legal than paying someone $37 for a utility to do it especially if you aren't selling the script.

Additionally, shell scripts aren't the only way to do it. There are plenty of scripting languages that work on all operating systems.

And I never asked for your "sensitive" data. All you had to do was give 1-2 lines of sample data (in which the actual data isn't so much important as is the same format (numbers where there are numbers, letters where there are letters).

Whatever.

 

[Jaybird180]

Wildwobby- no shape bending necessary, I just don't understand developer personality types and PoA threads are notorious for careening in unexpected directions.

There are no legal issues in manipulating csv data. It is after copy/pasting the data that it will then need to be changed to the proprietary format, and what the utility is purported to do; the vendor doesn't officially support it, but I did find a link to it in their support knowledgebase.

I asked about a script because I thought that would be a simple straightforward request, I told you I'm no developer - I did some program writing when I was 8 in BASIC and my college FORTRAN teacher was nuts, 60+ students dropped the class, I was one of the foolish 9 that stayed beyond the point of getting our money back.

I will look at the data and provide some headers...that is if you are still interested in helping the likes of me....[insert apology here]

 

[ebtech]

If I read the OP correctly, Jaybird isn't asking for a script to convert the files to the proprietary format. He has a shareware program to do that. The problem is it will only convert 100 lines of his multi 1000 line csv files at a time without paying for it.

I think he's asking for a script to export 100 line "slices" from his csv files into multiple 100 line csv files for testing the conversion program.

Is that correct?

Edit: A quick google search yielded this.

http://www.freewaregenius.com/how-t...-csv-file-by-a-specific-number-of-lines-rows/

 

[Jaybird180]

If I read the OP correctly, Jaybird isn't asking for a script to convert the files to the proprietary format. He has a shareware program to do that. The problem is it will only convert 100 lines of his multi 1000 line csv files at a time without paying for it.

I think he's asking for a script to export 100 line "slices" from his csv files into multiple 100 line csv files for testing the conversion program.

Is that correct?

Edit: A quick google search yielded this.

http://www.freewaregenius.com/how-t...-csv-file-by-a-specific-number-of-lines-rows/

YES!!!! I guess there really is an app for "THAT"

Thank you. I will try this tomorrow!

 

[LDJones]

YES!!!! I guess there really is an app for "THAT"

Thank you. I will try this tomorrow!

Why not suck the CSV into Excel, then save out your 100 row segments one by one? Even with a few thousands lines you're only talking 15 min of effort.

 

[poadeleted21]

If I read the OP correctly, Jaybird isn't asking for a script to convert the files to the proprietary format. He has a shareware program to do that. The problem is it will only convert 100 lines of his multi 1000 line csv files at a time without paying for it.

I think he's asking for a script to export 100 line "slices" from his csv files into multiple 100 line csv files for testing the conversion program.

Is that correct?

Edit: A quick google search yielded this.

http://www.freewaregenius.com/how-t...-csv-file-by-a-specific-number-of-lines-rows/

It takes a full blown 3rd party GUI to do what split,xargs,head and awk will do in one shell command?

 

[EdFred]

Why not suck the CSV into Excel, then save out your 100 row segments one by one? Even with a few thousands lines you're only talking 15 min of effort.

You can even do that with a macro in excel to save the data to new files.