Restructuring Text file to CSV

scottalanmiller

So what would an IDEAL format look like?

scottalanmiller

Would this be good?

2015-09-23,12:18:46.000

gjacobse

More along the lines of this:

2015-09-23	11:48:25.000
2015-09-23[tabbed space here]11:48:25.000

gjacobse

I should add, that this will be a Crystal Reports Export to be done by another party.

not that that should make any difference.

scottalanmiller

So you are wanting a TSV, not a CSV?

scottalanmiller

This is super easy. So assuming that your file is myfile.csv and you are on Linux, just do this...

unexpand myfile.csv > myfile.tsv

Like magic, Linux saves the day.

gjacobse

@scottalanmiller said:

So you are wanting a TSV, not a CSV?

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

However, TSV is not a Text file option in Excel - Only CSV

scottalanmiller

Excel doesn't know formats, it just knows extensions Windows apps are not very smart.

gjacobse

Also, all the data is [TAB] not [,]

gjacobse

@scottalanmiller said:

Excel doesn't know formats, it just knows extensions Windows apps are not very smart.

I won't argue you there...

JaredBusch

@gjacobse said:

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

This would be Tab Separated Value

JaredBusch

How are you planning to resatructure this prior to importing?

You say a user is doing this? Then teach them how to handle it in Excel. You will be hard pressed to get a user to do much outside of an office application.

Please be more specific about what you are doing with these files.

who is exporting it and from where.
who is restructuring it
who is opening it in Excel
where is it going after that

scottalanmiller

LOL, right. It's Tab instead of space! The original file is an SSB, Space Separate Values.

gjacobse

@JaredBusch said:

@gjacobse said:

While I suppose technically speaking, yes it would be a TSV - Tab Space Value.

This would be Tab Separated Value

Yea,.. there is that...

scottalanmiller

So anyway, that one command solves the problem nice and easy.

gjacobse

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

scottalanmiller

@gjacobse said:

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

So does the command that I provided work? Can you provide full sample data so that we can test?

gjacobse

@scottalanmiller said:

@gjacobse said:

I expect that the 'best course of action' is to have the User rerun the export and adjust accordingly.

However I was hoping to perform a trial run of the import with the data I have, about 100 lines or so. But each row has seven (x)DATE items...

So does the command that I provided work? Can you provide full sample data so that we can test?

My apologies Scott, I missed your Linux command in a page refresh.

Sadly I don't have this file on a Linux system.. Windows only...

scottalanmiller

@gjacobse said:

Sadly I don't have this file on a Linux system.. Windows only...

So copy it, only takes a second.

JaredBusch

@scottalanmiller said:

@gjacobse said:

Sadly I don't have this file on a Linux system.. Windows only...

So copy it, only takes a second.

Maybe true, but my earlier questions remains unanswered. Those are more important than a simple "just copy it" answer.

He clearly stated this will be something repeated later with at least one more file. Thus I asked what the true purpose here is supposed to be.