Ask Your Question
1

How do I use a macro to input a UNICODE CSV file into values I can use in Base?

asked 2017-06-03 01:38:03 +0100

David_Ssc gravatar image

updated 2017-06-03 12:33:13 +0100

I have a Unicode CSV file with string and date values that I need to convert to values I can store in a Base table. I only use about 6 values of a 28 variable file so I was reading one line at a time and updating individual values in a Base table depending on the values I read in. My understanding is that I needed to convert the date values to YYYY-MM-DD from MM/DD/YYYY before I can import them into a Base table - is that correct?

What is the best way to handle this kind of import of a CSV file?

Appended 6/3/17: I have three computer-naive users of this database so I need the entire operation of opening and reading the Unicode CSV file performed by a macro. I am using SQL to update the records and Format(sDate, "YYYY-MM-DD") does not work on a Unicode string. So, if I can automate opening the file in Calc and then importing to Base, then I think I can read through the Base table to perform the other update actions that I need. Code would be appreciated. Thanks. Thanks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-06-03 02:38:25 +0100

Ratslinger gravatar image

updated 2017-06-03 02:45:29 +0100

The format of the date stored in HSQLDB (which is the database - Base is just the front end) and most DB's is indeed YYYY-MM-DD. However this need not be the format used to enter a date.

If, for some reason you really need a macro, just use basic to reformat the date to YYYY-MM-DD format.

An easy way to get CSV data into a table is by using Calc. First open the CSV in Calc. By doing so, the opening dialog allows you to select which columns you want. At the same time, you can specify the data type. These are done in the Fields section. When satisfied, select OK. Data is now on a Calc sheet.

You should have a table defined in Base to match the column sequence you are setting in Calc.

Now just copy all the data in Calc (less column titles), and on Base main screen, right click the table where the data is to be inserted into. Select Paste and this brings up the dialog which should be self explanatory.

Again (as you never marked your last question) If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more

Comments

1

@David_Ssc In future either respond with comment or append your original question. Answers are to reply to questions. Also, the checkmark (which can still be set) helps signify to others the question was answered correctly. It also gives Karma to the recipient allowing more functionality within the forum.

Ratslinger gravatar imageRatslinger ( 2017-06-03 04:13:03 +0100 )edit

Back to your further questions. If you are using SQL in macro for the update, then yes the date must be converted and input in the proper format. However it is not clear as to what four users has to do with the CSV conversion. Further explanation would be helpful. Is this a recurring process?

Ratslinger gravatar imageRatslinger ( 2017-06-03 04:15:45 +0100 )edit

BTW -

myDate = "02/01/2017"
reformattedDate = Format(myDate,"YYYY-MM-DD")
Print reformattedDate

It is pretty easy.

Ratslinger gravatar imageRatslinger ( 2017-06-03 04:53:09 +0100 )edit

As far as I can see, HSQLDB uses UTF-8 format. The exception is Text tables. These can have different settings. Other databases may be different.

There would not be any specific code to transfer Calc data to Base. Each would need to be written independently.

Ratslinger gravatar imageRatslinger ( 2017-06-03 07:10:56 +0100 )edit

Created my own UTF-8 file for testing. This works:

reformattedDate = Format(Mid(YOUR_TEXT_DATE,2,10),"YYYY-MM-DD")

Have now created a UTF-8 file, used a macro to read the file, converted the date and wrote the data to a table. Only four columns were involved but more would be negligible. All appears fine. Coding was way less than expected.

Ratslinger gravatar imageRatslinger ( 2017-06-03 23:39:32 +0100 )edit
0

answered 2017-06-03 04:00:04 +0100

David_Ssc gravatar image

updated 2017-06-04 14:12:16 +0100

I appreciate the option of going through Calc. What I am working towards is a database that four people will be using so the import into Calc and then transfer to Base should all be done by a single macro rather than by hand. It sounds like I don't have to reformat the date fields - which will reduce some of my coding issues. Are strings of Unicode text converted to ASCII before being saved in a Base table? If not, then they will take up twice the space.

Do you have a preferred macro code documented in this forum where the macro would open a Calc file and then transfer it into a Base table? (I am new to the forum and I thought I had marked my last question as closed because the answer had answered my question. I see now the checkmark for closing it the preferred way. Thanks, David

Update 6/4/17:

I can't tell all the specifics, but the file I was trying to import had a hexadecimal 00 as every other character. So, my final solution is this general purpose function for reading a CSV file that also fixes Date fields:

Function ReadCSV(n as integer)
Dim sLine as string
Dim sOut() as string
Dim i as integer
    Line Input #n, sLine    ' Read a line of data
    if InStr(sLine,chr(0))>0 then sLine = Replace(sLine, Chr(0),"")
    sLine = Replace(sLine, Chr(34),"")      'Removes quotes as text delimiters'
    sOut() = Split(sLine, ",")
    For i = 0 to UBound(sOut)
        if InStr(sOut(i), "/") > 0 then sOut(i) = Format(sOut(i),"YYYY-MM-DD")
      next
ReadCSV = sOut

End Function

edit flag offensive delete link more

Comments

Thanks for the response. My file shows no extra hex chars such as yours. Would not have known that was problem without copy of your data. The biggest difference between our routines is the removal of quotes. I do it on individual fields checking first, last char for quote & extract using 'mid'. This was in case there were any quotes within a text field so they wouldn't be removed.

Ratslinger gravatar imageRatslinger ( 2017-06-04 15:23:24 +0100 )edit

Better programming to remove quotes as the first and last characters - I will adopt. After working in the dark about what the format was, I did a Hex Dump of the file. Should have started there! Thanks for being patient with me while I learn this forum etiquette.

David_Ssc gravatar imageDavid_Ssc ( 2017-06-04 18:18:21 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-03 01:38:03 +0100

Seen: 351 times

Last updated: Jun 04 '17