Parse CSV files from Mac or PC Excel

General
21

Hi Tim, thanks for my stubbornness, I’m a little newbie.

1)In “Methods” I added a new module called: “GetOpenText”
2)The code that I pasted inside this new module is this:

dim fn As FolderItem
Dim sType as New FileType

sType.Name = “text/” + ext
sType.MacType = “TEXT”
sType.Extensions = ext

fn = GetOpenFolderItem(sType)
Return fn

  1. On the “Inspector Panel” I putted this properties to the new module:
    Method Name: GetOpenText
    Parameters: ext As String
    Scope: Public.

So I created a Window, and inside of It i made an action Button, with this code:

’ Set up and called like this:

dim f As FolderItem = GetOpenText(“csv”)
if f = nil then exit sub

dim c As new sbCSV
c.FileName = f

’ Accessing the filename will automatically load the CSV file.
’ The defaults are:
’ Delimiter = ,
’ TextDelimiter = "
’ Decimal = .
’
’ If you want to change these then do so BEFORE setting the folderitem. Like this:

dim c As new sbCSV
c.Delimiter = “;”
c.TextDelimiter = “'”
c.FileName = f

So when I run the program, it shows me that error:

This item does not exist
dim f As FolderItem = GetOpenText(“csv”)

Cant find a type with this name
dim c As new sbCSV

Cant find a type with this name
dim c As new sbCSV

What I’m doing wrong, Am I forgetting something?

22

[quote=93984:@Christian Schmitz]Well, first we have a XL plugin to read/write excel files without the need of the Excel application.

Second you can simply read CSV with textInputStream class.
For splitting CSV lines, I once put a lot of work into the SplitCommaSeparatedValuesMBS function in our plugins.
(to handle quoting correct)

Maybe that helps you.[/quote]

I have the XL plugin installed, I paste this code on the “Open event” on a new window:

dim book as XLBookMBS(false)

dim sheet As XLSheetMBS = book.AddSheet(“Sheet1”)

call sheet.WriteString 5,1, “Smith & Co”
call sheet.WriteString 6,1, “First Avenue 123”
call sheet.WriteString 7,1, “12345 New York”
call sheet.WriteString 8,1, “Bob Smith”

dim file As FolderItem = SpecialFolder.Desktop.Child(“test.xls”)

if book.Save(file) then
file.Launch
else
MsgBox “Failed to write:” + book.ErrorMessage
end if
quit

in order to test the plugin and make a simple Excel File, but it shows me that error:

Syntax error
dim book as XLBookMBS(false)

Am I wrong?

23

the new is missing:

dim book as new XLBookMBS(false)
24

So if you created the method before you created the window, where did you create the method? “Methods” of what? A module? App? If you created it in the App itself, you must use “app.” to access it: app.GetOpenText("csv")

25

Ok I Placed the “Method” inside of SBCSVin, now I made the Module Inside of “App”

Now In a new window, inside an action button I paste the code, and Now I can Invoke the module like you said.:

dim f As FolderItem = App.GetOpenText(“csv”)

if f = nil then exit sub

dim c As new sbCSV
c.FileName = f

But now the Issue is when in run it, it says:

Can’t find a type with this name
dim c As new sbCSV

I assuming that it would be “sbCSVIn” instead of sbCSV.
Its that true?
Thanks

26

Assuming that would be “dim c As New sbCSV” I got no errors.
So, when I paste this code to access the values, I paste this,inside of the action button:

’ Once you have loaded the CSV file then the values are accessed through these methods:

dim st As String = c.StringValue(row, column)
dim i As Integer = c.IntegerValue(row, column)
dim curr As Currency = c.CurrencyValue(row, column)
dim dbl As Double = c.DoubleValue(row, column)
dim dt As Date = c.DateValue(row, column, “yyyy-mm-dd”) ’ last parameter is the date format of the CSV file.

’ There are two other properties worth mentioning:
c.ColumnCount ’ returns a 1-based number of columns (Fields) in the CSV file. This is the MAXIMUM number.
c.RowCount ’ returns a 1-based number of rows in the CSV file.

’ Errors
c.Error returns true if there were errors in reading the CSV file
c.LinesInError is a 0 based list of the error lines if there was an error.

’ Error Useage
TextArea1.Text = “”

if c.Error then
for lp as integer = 0 to c.LinesInError.Ubound
TextArea1.AppendText c.LinesInError(lp) + EndOfLine
next
end if

And it shows me that errors:

This item does not exist
dim st as String= c.StringValue(row,column)

This item does not exist
dim i as Integer= c.IntegerValue(row,column)

This item does not exist
dim curr as Currency= c.CurrencyValue(row,column)

This item does not exist
dim dbl as Double= c.DoubleValue(row,column)

This item does not exist
dim dt As Date = c.DateValue(row,column, “yyyy-mm-dd”) 'last parameter is the date format of the CSV file

Can’t call something that isn’t a function
c.ColumnCount 'returns a 1-based number of columns (Fields) in the CSV file. This is the MAXIMUM number

Can’t call something that isn’t a function
c.RowCount 'returns a 1-based number of rows in the CSV file.

This item doesn’t not exist
c.Error returns true if there were errors in reading the CSV file.

Can’t call something that isn’t a function
c.LinesInError is a 0 based list of the error lines if there was an error.

I ask you something, Can be possible to get an Example file of the sbCSVin, for study it and understand it?
Thanks