CSV File with Spaces

HCL Domino Domino Forum
1

Hi,

I’ve Seen some posts regarding this before with no definite answer (as far as I can see).

I receive a CSV file which needs to be imported automatically on a daily basis.

I was trying to use:-

Input #fileNumber , variableList

to get the data and then create/update my documents but there is a problem. When I look at the variables, it seems to assume that if there is a space, that should be a new variable. This is throwing out my import routine completely. I always thought this used the comma separator (obviously not).

I would use Line Input and then parse the line at each comma but , I do have some address fileds which have commas in (which are surrounded by quotes) . I am assuming that the only way I can do this properly is to have each value surrounded by quote.

So, the question is, can i do this in script with the file as it is? I am trying to get the sender of the file to include quotes around each field but I think they do it in excel which only seems to add them if there is a comma in the field (is that correct?). can that be changed in excel?

Thanks in advance

2

Subject: CSV File with Spaces

I’ve just tested this using VB6 and it does work using just the commas. I knew it had worked somewhere before for me.

3

Subject: CSV File with Spaces

This is from the Domino Designer Help:

’ In this example, LotusScript reads the contents of a

’ comma-delimited ASCII file (c:\123w\work\thenames.txt)

’ into an array of RecType. RecType is a user-defined

’ data type.

’ c:\123w\work\thenames.txt consists of the following:

’ “Maria Jones”, 12345

’ “Roman Minsky”, 23456

’ “Joe Smith”, 34567

’ “Sal Piccio”, 91234

Type RecType

empId As Double

employee As String

End Type

Dim arrayOfRecs() As RecType

’ A dynamic array that will get sized to

’ the number of lines in c:\123w\work\thenames.txt

Dim txt As String

Dim fileNum As Integer

Dim counter As Integer

Dim countRec As Integer

’ Get an unused file number so LotusScript can open a file.

fileNum% = FreeFile()

counter% = 0

Open “c:\123w\work\thenames.txt” For Input As fileNum%

Do While Not EOF(fileNum%)

’ Read each line of the file.

Line Input #fileNum%, txt$

’ Increment the line count.

counter% = counter% + 1

Loop

’ Return the file pointer to the beginning of the file.

Seek fileNum%, 1

’ The file has counter number of lines in it, so

’ arrayOfRecs() is defined with that number of elements.

ReDim arrayOfRecs(1 To counter%)

’ Read the contents of the file into arrayOfRecs.

For countRec% = 1 To counter%

Input #fileNum%, arrayOfRecs(countRec%).employee$, _

  arrayOfRecs(countRec%).empId#

Next

Close fileNum%

Print arrayOfRecs(2).employee$ & " " arrayOfRecs(2).empId#

’ Output:

’ Roman Minsky 23456

4

Subject: RE: CSV File with Spaces

Hi Vilhjalmur(sorry if this appears twice, I’m sure I sent this earlier but I can’t see it)

Anyway, thanks for the info. I tried this with my data but, if there is a ‘column’ with nothing in it, it throws up a type mismatch. Any ideas how I can cope with ‘missing’ data?

Thanks

5

Subject: Free Lotus Notes/Domino Import Utility

We have a Lotus Notes / Domino Import utility which you can freely download from our website. Written totally in Lotusscript, it handles importing from CSV, MS-Excel, MS-Outlook and other character delimited files.

It handles the problem you are experiencing plus automatic scheduled importing is a standard feature of the product. The application is easy to use, has full help information and we fully support it.

To download it now click the following link:

Regards,

Alex

6

Subject: RE: Free Lotus Notes/Domino Import Utility

thanks alex, I’ll check it out.