Sublime Forum

Nightmare CSV-any suggestions using this awesome app?

#1

Hi,
I am a new user/buyer of this fantastic app and I suck at excel formulas and I’m just learning scripting. I have a spreadsheet that has a few columns and I need to create a csv file that is formatted specifically so that I can import the data to an app.

The spreadsheet has 4 columns, and I need to concatenate the columns and then also add some " marks and ; marks, and then a couple of values that are not in the spreadsheet.

ColumnA=SomeName --> x.x
ColumnB=This.Keeps.Failing
ColumnC=A description of my system in column A
ColumnD=a folder name

I need to take these columns and combine them to form a string like the one below, which doesn’t have the data from Column C - that might be removed from my requirement as the vendor might not support the description field any longer. I don’t have a column that has the “Type” value in it that you see in the string below, I was hoping that could be part of a formula, and then I don’t have a column with the 123 number as well as that could change as that number is specific to a site, so I wanted that to be part of the formula.

Any suggestions on how to do this using sublime? It’s got to be better than miserable excel :imp: :imp: :imp:

I have no " marks around any of the items in the spreadsheet, I figured it would be easy adding them with an excel formula, but I was wrong, and aside from that everything else is mentioned. Thank you all!

GeorgiaSite1 --> x.x.x;“Test.this.fails”;"";"";“type”;“0123”;“Test-subfolder”

0 Likes
#2

Excel can be tricky, but that’s probably the easier way to go. Using your data, I came up with this excel function:

="GeorgiaSite1 --> "&A1&";"""&B1&""";"""";"""";""type"";""0123"";"""&D1&""""

That will output the exact string you have listed in your post: GeorgiaSite1 --> x.x.x;“Test.this.fails”;"";"";“type”;“0123”;“Test-subfolder”

Is that what you’re trying to do or am I misreading your question? :smile: As far as doing this in SublimeText… technically you could make it do just about anything with the right amount of Python knowledge - but given a set of data (in csv format), Excel really would be easiest.

0 Likes
#3

I found this post looking for a good syntax package in Sublime Text 3 to help draft complicated Excel formulas, like:
=IF(NOT(OR(Table13[@[No Valve Number]:[No Dot]])),IF(COUNTIF([@[after dash]],"*-*"),MID(D531,FIND("-",D531)+1,FIND("-",D531,FIND(".",D531))-FIND("-",D531)-1),MID(D531,FIND("-",D531)+1,LEN(D531)-FIND("-",D531))),"")

VBScript, available in Package Control and at https://github.com/SublimeText/VBScript was just the ticket. It even indented when linefeeds were added for clarity inside the IF/THEN statement.

0 Likes