[Solved]User-defined date format (yyyymmdd) & data to column

[Zizi64]

Converting a .csv format data file
into (temporary) .ods format (when you edit a spreadsheet document then the file exists in ODF format)
then into .xls format (what is incompatible with the editor software)
then into some database format:
it is a nonsense - in my opinion...

I suppose you want not make any calculations in the database neither with the datas nor dates ...

[Kprogrammer]

Converting a .csv format data file
into (temporary) .ods format (when you edit a spreadsheet document the file exists in ODF format)
then into .xls format (what is incompatible with the editor software)

then into some database format:
it is a nonsense - in my opinion...

Can you suggest any alternative for this?

I suppose you want not make any calculations in the database neither with the datas nor dates ...

Yes exactly.

[Zizi64]

Converting a .csv format data file
into (temporary) .ods format (when you edit a spreadsheet document then the file exists in ODF format)
then into .xls format (what is incompatible with the editor software)
then into some database format...

Can you suggest any alternative for this?

You can eliminate some steps:
- Do not use the obsolete .xls file format. Save the converted data into .ods file format: then the result will be 2 steps instead of 3 steps.
- Try to import the data directly from the .csv into the database format, and modify the data with the Base component. It is 1 step only.

[Kprogrammer]

Converting a .csv format data file
into (temporary) .ods format (when you edit a spreadsheet document then the file exists in ODF format)
then into .xls format (what is incompatible with the editor software)
then into some database format...

Can you suggest any alternative for this?

You can eliminate some steps:
- Do not use the obsolete .xls file format. save the converted data into .ods file format: then the result will be 3 steps instead of 4 steps.

- Try to import the data directly from the .csv into the database format, and modify the data with the Base component. It is 1 step only.

Thanks for the helpful suggestions.
I will try this, it's better.

[Villeroy]

Where does the csv come from? A database? Do you have access to that database or do you only get the csv from it?

[Kprogrammer]

Where does the csv come from? A database? Do you have access to that database or do you only get the csv from it?

I only get the file in csv format. I don't know much about it.. using this i prepare a File which has columns , as the fields of database. So that these data can be inserted into database.

[Villeroy]

And you have thousands of these text files?
Write a simple script to merge all these files into one (skipping the first row of column labels) and most of the work is done.

How to import such a table into a "Base database" which is a HyperSQL database actually:
Put your text file as Import.csv in the same directory as the attached Base document.

The Base document consists of 3 tables:
1) A text table "CSV_IMPORT" which is linked to Import.csv with the given structure of your example csv file. The text table consists of 3 text fields and one field of decimals. The US-dates are interpreted as text.
2) A view which selects the 4 columns and converts the US-dates to database date values.
3) A normal data table with 2 text fields, one date, one decimal and an auto-incrementing row number as primary key. The data table also includes a unique index on the product code, the reference code and the date so it is impossible to import duplicate rows with the same pcode, date and refcode.
There is also a query in the database which stores the source code of the view.

You import the data from Import.csv like this:
Copy the IMPORT_VIEW icon (do not open it).
Select the DATA table icon and paste (do not open).
A dialog pops up with the name of the target table and option "append data" checked.
Click [Create] since the columns in the view are in the same order as the table columns and the data types fit to each other.

[Villeroy]

Write a simple script to merge all these files into one (skipping the first row of column labels) and most of the work is done.

The attached Python script does it. It is very simple and not well tested. I'm a lousy programmer.

If you have Python installed on your WIndows box, you know how to start the script with the 4 arguments described below.
If not, you can use the Python runtime that is installed with your office suite as an alternative macro language. My script is NOT a macro. It is a little program.
On a Windows box you can call the script like this (I did not test it on Windows):

Code: Select all

C:\Program Files\OpenOffice4\program\python.exe D:\Script\MergeFiles.py D:\csv-collection "*.csv" Import.csv 1

The command is the path to python.exe in the program directory of your office installation path, for instance C:\Program Files\OpenOffice4\program\python.exe
Second part is the script file, for instance D:\Script\MergeFiles.py

The script file takes 3 or 4 arguments:
1. the directory of the files to be merged, for instance D:\csv-collection
2. a shell pattern in double-quotes "*.csv" points to all files ending with .csv
3. the name of the output file, for instance Import.csv
4. optionally, the count of column header lines to be skipped, by default 0, in the example command 1 (as in your csv example)

The script opens all files matching the given shell pattern (argument 2) within the given directory (argument 1) and appends all lines to the given output file (argument 3) skipping any given amount of lines on top (integer argument 4). For each processed file it prints the file name and count of processed lines to the terminal and moves the file to a subdirectory named "merged".

If you have my database document in the same directory, you can simply copy the merged data of Import.csv to the data table as described in the previous post of mine.
As more csv files arrive in the directory you can merge them to Import.csv, open or reload the Base document and copy Import.csv to the data table.

[Kprogrammer]

@Villeroy

Thank you so much Villeroy for providing such a great solution.You have explained it very well.
I understand what steps to follow. I will try to implement this.

Thank you once again!!