Is it possible?

Scripting Forums AppleScript | Mac OS X
#1

Here’s the deal.

I’m an AppleScript newbie. Have played around with the “elementary” stuff but I have found a useful purpose for a script and don’t grasp everything involved in it just yet.

Here is the situation. I receive a monthly report every month. If I export it from excel, the data is formatted like this:

23002,$350.47 ,$361.27
18th Street Market,
18th St,
N Little Rock AR 72554,
,
50366,$0.00 ,$5.40
88 Lone Elm Inc,
11920 Old 4388 Rd,
Rawdy AR 75042,
,
26887,"$1,091.15 “,”$1,528.78 "
Alabama State Prod,
c/o June Hersey,
P O Box 58855,
Dothan AR 36822,
,

This is the way I want it formatted:

23002,$350.47 ,$361.27,18th Street Market,18th St,N Little Rock, AR, 72554,
50366,$0.00 ,$5.40,88 Lone Elm Inc,11920 Old 4388 Rd,Rawdy, AR,75042,
26887,"$1,091.15 “,”$1,528.78,Alabama State Prod,c/o June Hersey,P O Box 58855,Dothan, AR, 36822,

Is this possible to do in Applescript or should I look at something else?

Thanks for your help.

#2

It’s certainly possible to do in AppleScript, but some clarification is needed, first.

To start with, it looks like the field delimiters are double-comma, right? Strange, but OK.

If that’s the case, why is there not a double-comma at the end of the first line (the one with the numbers on it)?

Lastly (for now) there is an inconsistency in the numbers line between your records. In the first two records they are like:

but in the last one the currency values are quoted, with the trailing spaces within the quotes, and the trailing quote isn’t present in your reformatted example.

These may seem like picky issues, but they can be relevant in writing a robust script.

#3

Hi Camelot,

I think it’s comma delimited from the spreadsheet.

gl,

#4

The data came from an excel spreadsheet that was saved as a csv file. I opened the file in Tex-Edit plus and copied and pasted into this forum.

There are no commas after the first row. Don’t know why.
I suppose excel put the double commas in.
the quote marks are random and I don’t know what they do.

In Excel, the data is formatted like this, with cell reference numbers in ()'s.

23002(A1) $350.47(B1) $361.27 (C1)
18th Street Market (A2)
18th St (A3)
N Little Rock AR 72554 (A4)

(could it be that the double comma’s are place holders for the B2 and B3 that aren’t present?)

Hope this helps.

#5

This is indeed a comma-separated file. Xcel (and I assume other spreadsheets) pads any lines with empty cells out to the total number of columns being exported.

In this case, there are 3 columns (A, B, C). Since the first line of each set uses all three columns, there are no trailing commas. The remaining lines in each set uses only column A and therefore pads the line with 2 trailing commas.

The quotes issue is simple as well (I say that because I have seen this MANY MANY times [I’m a graphics artist and I receive custom files frequently]). The number values greater than 999 are formatted to use commas and therefore must be quoted in the exported file.

I am at work right now, so I don’t have time to write the code to do the conversion. If you would like to email me an example text file, I should have some time over the weekend to work on it.

Brad Bumgarner, CTA