Restructuring Text file to CSV
-
So you are wanting a TSV, not a CSV?
-
This is super easy. So assuming that your file is myfile.csv and you are on Linux, just do this...
unexpand myfile.csv > myfile.tsvLike magic, Linux saves the day.
-
@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
-
Excel doesn't know formats, it just knows extensions
Windows apps are not very smart. -
Also, all the data is [TAB] not [,]
-
@scottalanmiller said:
Excel doesn't know formats, it just knows extensions
Windows apps are not very smart.I won't argue you there...
-
@gjacobse said:
While I suppose technically speaking, yes it would be a TSV - Tab Space Value.
This would be Tab Separated Value
-
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
-
LOL, right. It's Tab instead of space! The original file is an SSB, Space Separate Values.
-
@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...
-
So anyway, that one command solves the problem nice and easy.
-
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...
-
@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?
-
@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...
-
@gjacobse said:
Sadly I don't have this file on a Linux system.. Windows only...
So copy it, only takes a second.
-
@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.
-
@JaredBusch said:
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.
Exporting IT Tickets from TrackIT to be imported into SpiceWorks
-
@gjacobse said:
Exporting IT Tickets from TrackIT to be imported into SpiceWorks
Ah, then I would do what @scottalanmiller said. Copy it to a Linux system quickly and convert it.
Alternately staying in WIndows you could use Notepad++ advanced find and replace features to pattern match and replace the space with a tab.
-
Notepad will do the same thing.
-
Yeah, Notepad replace supports tabs, I just tested it. You can't easily replace a space with a tab, because your file will full of spaces in other columns that you don't want replacing.
I would probably do a replace of '[space]12:' with '[tabbed space]12:', so that it replaces all the spaces before a 12: in the time with a tab, and then repeat this for 01:; 02:, 03: etc etc upto 23: (assuming a 24 hour clock). It does mean running a search and replace 24 times which is a pain. There are also likely to be tabs added elsewhere where you didn't want them, such as in the body of the helpdesk tickets.
What I'd actually do is use the wonderful, and under-rated, Microsoft Access. Access is a fantastic text manipulation tool. It's very powerful, but you need a bit of experience to get it working.
