Noob here on W11 Pro, with MS Office 2000/Access and LO 25.8. I also have LO 25.8 on my LinuxMint 22.2 SSD.
I get our credit card data via a CSV download file. Rearrange it using CALC, adding a Header, etc, saving it as an .xls file. With Access I can click some buttons and my .xls file is appended to my mdb file. very simple.
I’ve tried a copy/paste–but everything gets copied into the cell I point to to start the paste. I’ve tried to save the ods file to a dbf file, but Base does not ‘see’ it where I put it.
Is there an easy solution for this once a month chore? I’m sure other folks have this problem, but after an hour of looking for recent posts–I haven’t found any.
Ron
CSV is not a spreadsheet format. It is not even a file format. CSV is a convention to exchange data between databases.
- Base is not a database. It is a tool to work with databases. You don’t tell us the type of database you are working with. It is indicated in the status bar of your loaded Base document.
- You don’t tell us anything about your specific type of CSV. The type of csv, in particular the way how numbers and dates are encoded, is the most essential info before you can do anything with CSV data.
I can offer a simple Basic routine able to import any flavor of CSV into a HSQLDB without involving any clueless spreadsheet program.
I’ve looked thru the link and tried the copy & paste. My .ods file had the 1st line header, but did not have the index column. It failed with a popup error “Wrong data type: java.lang.IllegalArgumentException
Continue anyway?” Yes but nothing as added.
I added the index column to my .ods file but got the same error.
I had trouble getting the correct JRE installed, since my system is 64 bit and I read somewhere LO 25.8 needs a 32 bit JRE, so I installed that, and then LO worked. LO also shows JRE is installed. There is difference tho, my dates are format 01/01/2025, with no time, some of my columns 32 & 50 character wide. However, except for a few columns which might be different, the column formats are the same in my Base table and my .ods file. Any idea what this means?
It is impossible to tell anything helpful without knowing your source file nor target database.
Villeroy,The previous post was meant for robleyd. this is for you. I did not mean that the csv file was what I used–it’s the format that the bank has me download. It is then copied into CALC and then I install the header, columns are rearranged and after that my data is added. Then it is usually saved as an .xls (MS Excel) file to import into Access. But while trying to get Base to work, I also save the file as .ods file and use that to copy from. Also On the very bottom it says Embedded Database and to the right is says HSQLDB Embedded.
If you want I will post a 2 line odb file, and a 2 line .ods file on Dropbox. I can’t see and icon to post it here.
See this topic; you may find other useful information in the rest of the topic.
My response would be essentially the same. The error “Wrong data type" gives a hint but without seeing the actual data…
OK–I have no up pointing arrow so I can’t upload anything here, perhaps I am new and don’t have enough posts yet??
I had some things different between my Table and my ods file, which I fixed, like the Date format. I finally got it appended it BUT I had to add the additional index number in the correct order. iow I had 2 lines in my Table, so the index # were 1&2. I also had in the ods file index column 1 & 2 for the 2 rows I copied–hence the error. I had to change the the ods index column to 3&4 and then it pasted it perfectly. I need a way to not count how may rows I have in the downloaded file and add those to the amount I already have in Table so I can copy/paste. In my table–the cell below the last index number has “Auto”–is that just for a Form when inputting data? I don’t manually input any data. Is there a solution for that? As you probably know MS Access does not use index #.
I don’t want to see the wrong results of your import. I want to see the csv.
Upload 2 lines of plain text from a text editor as a code block (below 3 backticks).
Alternatively, open a Writer document and call menu:Insert>Sheet from file… Choose the csv file, which will be imported literally.
Status,Date,Description,Debit,Credit,Member Name
Cleared,05/24/2025,“WAL-MART #2485 PONDERAY ID”,72.63,MARIANNE
Cleared,05/22/2025,“WAL-MART #5883 SPOKANE VALLEWA”,22.41,MARIANNE
This was just downloaded from my bank. This is opened in CALC and I add the header (see below), copy the columns and put them in the correct place, etc and then save it as an .xls file to be imported into ACCESS. VERY simple. My goal is to save it as an ods and import into Base–but I guess it will not be quite that simple.
btw, I am using LO 25.8.2.2 and it does not have a Isert>Sheet from file. It has a Insert>Text from file–which I used.
My header is:
WHERE WHAT WHY TO_WHOM AMOUNT DATE_BOT DATE_PAID CK_CA Don’t Count
My mistake was reading info on the web, some of which comes from LO, such as “Import Data from ODS, Go to the menu and Select File>Import. Choose Spreadsheet from the options” The version I have does not have an “Import”.
After “playing around” with copy/paste I have the info I need to proceed–I THINK and I still have to try all this on Linux before leaving M$ W11. Since I don’t care about index #, I will add the ID column but leave it blank, copy & paste and let Base add whatever to that column it wants. My sorting will be MY columns: Dates, Where, To whom, etc
So thanks for the help from both of you.
I guess, the forum software replaced straight double-quotes with typographic ones.
So we have
- one row of 6 unquoted column headers
- unquoted text field
- dates in USA style
- a quoted text
- a point decimal
- another unquoted text
- columns separated by comma
- column “Description” enclosed by double-quotes as text separator
In order to import correct data from this flavour of csv into Calc, you would have to fill out the import dialog like this:
Give me a little bit of time for a simple Base solution with a file picker dialog importing the csv data in right column order without data type errors and skipping any duplicates being already in the database.
CSV as code block:
Status,Date,Description,Debit,Credit,Member Name
Cleared,05/24/2025,"WAL-MART #2485 PONDERAY ID",72.63,MARIANNE
Cleared,05/22/2025,"WAL-MART #5883 SPOKANE VALLEWA",22.41,MARIANNE
Additional question:
- Are there any negative amounts? How are they encoded?
(123.45)or-123.45or123.45-? - Are there thousands separators as in
1'234.56?
the following is from the raw input (with code quotes from me)
PLEASE DO NOt WASTE YOUR TIME… I have successfully “imported” 3 months of .xls (CALC exported) into my Base table. AS Mention before—The downloaded CSV file is changed to .xls or .ods in CALC and it is then deleted. I ONLY use the CSV to download–not to use. I’ve been doing this since 1999–so it is embedded 
Thanks again.