CSV or excel file importing help?

[datacaster]

I just imported a CSV file to create a table through tools import excel. Now I take that same CSV file and it says it does not match and will not import using a Alpha predefined button to import excel data.
I next tried tools and it imported the same spreadsheet with duplicate data and it created 3 new rows of the same data. I used the same spreadsheet twice being trying alpha predefined import button and the tools import function.

What I need is we get emailed several different spreadsheets (one spreadsheet from each building) on the first of the month that gives electric usage for each electric meter at each location being several different buildings. Each month I would like to be able to import each buildings spreadsheet and save the new months meters data. Enter the electric bill for that buildings location and calculate the kWh cost and then take the electric meters months kWh usage and bill the tenant from that.

Not sure how to do importing of 5 different buildings spreadsheets of data and have them add to the table the new data. Each spreadsheet is a building location with a group of many meters and in spreadsheet each row is a meter which has meter number, date period 12-1-19 to 12-31-19 and kWh usage.

Confusing as I don't really explain things well!

Thank you for any help in advance!

Paul

[Ted Giles]

Re: CSV or excel file importing help?

I would recommend a standard spreadsheet layout in a specific saved format.
If you are importing data it's important to have consistency.
They should be named as separate entities and have the same version number which they are saved as, say 2013. Excel should be able to save as a legacy version.

Or, you could have an on-line data capture process with data checking and excel download which is easy to do.

If you want to see a simple example, contact me through the details below.

[MoGrace]

Re: CSV or excel file importing help?

A sample of the spreadsheet (csv?) and resulting table data would be helpful.

[Al Buchholz]

Re: CSV or excel file importing help?

Paul

Importing excel spreadsheets is a tricky process since Excel allows and defines multiple data types within the same column. A database doesn't like that.

IE, a column that starts with character values in a column will allow numeric data in subsequent rows of that column.

You can see that by what data imports successfully and which doesn't.

You can also see that by importing each sheet separately and seeing the different table structures that are generated in Alpha.

To fight that battle is a tough one.

It is not a matter of importing and appending as a simple process.

As Ted stated, you may want to explorer another way of inputting data that provides consistent data entry.

IE build an application in Alpha and put that on each desktop with a runtime copy or build an web based data entry.

An Alpha browse can provide consistent data entry to each user and the merging of data is simple again.

[datacaster]

Re: CSV or excel file importing help?

This is the CSV file I get from the meter company attached. I have no control over how it is formatted. The CSV file comes from a web application the meter company has where the meters are grouped based upon location and the CSV file sent each first of the month.

The problem is in order to get the money owed calculated using the meter companies web application, each location requires that you enter the power bill for each meter at that location. With say 256 meters it would be too much work for me to enter the same electric bill 256 times to calculate what is owed.

I want to just enter the power bill once for each location and get the bills calculated from the monthly spreadsheets in the Alpha App created for this. A new power bill will have to be entered in this Alpha application each month to calculate what is owed as we cannot charge more than we pay for electricity. We can only pass the cost on. It would simply take the kWh total at location and the cost for the total to get the kWh cost for 1 kWh.

Thanks!

[Ted Giles]

Re: CSV or excel file importing help?

@Al
I am making a presumption that the Excel is pretty much standard, so we should be able to analyse it and capture the data.
Having said that, there are people involved, so there could be multiple levels of crapola.

If the Excel file names are any way similar, like "Building 1_01/01/2019" it could be possible to create a var string and pick up the correct data.
Then again, importing to a SQL table might be an option.

Automating an infinitely variable import just won't work.

I have a copy of the CSV so I'll fiddle with that.,

[datacaster]

Re: CSV or excel file importing help?

The file names once imported will be always the same, except the date will change to say it is the beginning of the month, so 12/1/19, 1/1/20, 2/1/20 is the only changing part of the CSV filename. The data in the CSV rows after the description row will always be different. The manufacturer that wrote the web portal software to enter electric costs feels it is better to enter the cost 256 times a month versus one entry once a month. Go figure the logic there. Also I realized as a application, I really need to import each CSV file with a button on a form and have it create the table to store the data. I guess when it see the same building name it would just add to a existing table and if new create the new table based on the new CSV. Probably way too complicated, just thinking of end product and when I am gone, how do they add a new building and then add each month to that newly created table.

Thanks for all the thought on this. Maybe I was just overthinking this and I should just kiss off the CSV import and make it a manual entry method where you add each location and then each meters for each location once so they are grouped to a location and then enter the bill for each location. Unfortunately that then means entering each meters usage each month which is a lot of work too to do meter by meter.

Thanks All for your support!

Paul

[Al Buchholz]

Re: CSV or excel file importing help?

Paul

All of this can be done in an automated fashion.

But it'll take some coding to handle all of the issues.

The import tools handle the easy stuff - this needs to be able to handle harder stuff.

[Ted Giles]

Re: CSV or excel file importing help?

OK, Paul
I have a process which takes a CSV and creates a table from a button.
It can be run many times, and just adds the data to the table, so running your example 3 times gives 9 records.
I have not added any Duplicate Checking - that would be the next step.

So,processing wise, would you be able to accept the CSV's as sent to you and rename each one to a standard naming convention, then run the import and add the data to the base import file?

Example;

CSV 01012019 becomes CSV_Import - run the process - rename or delete from the folder
Next CSV
CSV 01022019 becomes CSV_Import - run the process - rename or delete from the folder

Or, it should be possible to capture a variable - which you would enter to create the import string.

I'm assuming that once you have the data in a Table, you are able to chop it up as you wish.

Let me know please.

[MoGrace]

Re: CSV or excel file importing help?

what are the descriptions of the columns?

[Ted Giles]

Re: CSV or excel file importing help?

The string at the beginning is the field content/types Robin.
178 fields.

[Mike Wilson]

Re: CSV or excel file importing help?

I have read through this thread over and over and I cannot wrap my head around the exact problem. I import data in CSV files extensively from many companies and there is no scenario yet that I haven't been able to make work. Like Al said, it might take upfront mapping work, but once done, it is done.

Paul,
Database strategy and solutions are a seriously detailed business. Logic and structure are critical and help will occur when you meticulously describe the structure and details of your situation and the objective. First things first.... get the data from spreadsheet to table. I don't understand why there is discussion about importing the CSV data to new tables? I must be missing something here. One table with a field that is a concatenation of building number and Meter Number should suffice as a unique account number and should be completely sufficient to filter records and all records reside in a single table. But truly getting the details of what the data import situation is needs better description...

...Each month I receive five different CSV spreadsheet containing data for electricity consumption.
...The 5 spreadsheets are electricity consumption for 5 different buildings, one spreadsheet for each building.
...Each building has multiple customers. Each customer has a unique meter having a unique meter number.
...In all spreadsheets, each row is a unique record of data collected by a unique meter that corresponds to electricity usage for a unique customer for the month.

>>> THE CRITICAL PIECES - WHICH ARE THEY IT? <<<<<
1: INTRASPREADSHEET CONSISTENCY: BUILDING LEVEL CONSISTENCY:
A - The spreadsheet for a building will always have the same field order - the columns in the spreadsheet are always the same and in the same order.
B - The spreadsheet for a building will NOT always have the same field order - the columns in the spreadsheet with vary month-to-month
2: INTERSPREADSHEET CONSISTENCY: CONSISTENCY BETWEEN SPREADSHEETS
A - The field order for each of the spreadsheet for the 5 spreadsheets ARE THE SAME for all spreadsheets.
B - The field order for each of the spreadsheet for the 5 spreadsheets are not the the same for all spreadsheets and do not match.

This sets the stage for understanding what you are facing. Until this is understood, we are all spinning in assumptions.

[MoGrace]

Re: CSV or excel file importing help?

The string at the beginning is the field content/types Robin.
178 fields.

My bad - I opened with Libre and it started on row 2

[datacaster]

Re: CSV or excel file importing help?

OK, Paul
I have a process which takes a CSV and creates a table from a button.
It can be run many times, and just adds the data to the table, so running your example 3 times gives 9 records.
I have not added any Duplicate Checking - that would be the next step.

So,processing wise, would you be able to accept the CSV's as sent to you and rename each one to a standard naming convention, then run the import and add the data to the base import file?

Example;

CSV 01012019 becomes CSV_Import - run the process - rename or delete from the folder
Next CSV
CSV 01022019 becomes CSV_Import - run the process - rename or delete from the folder

Or, it should be possible to capture a variable - which you would enter to create the import string.

I'm assuming that once you have the data in a Table, you are able to chop it up as you wish.

Let me know please.

Hi Ted,
Sorry for big delay.

In this CSV file there are really two records in theory, I guess as the first row is the actual description that appears for each column and will appear in every CSV file for all locations.

Each CSV file that is emailed for processing will consistently have a filename "monthly_data_location name_report date generated-timestamp" where "location name" will be different for each csv file. It's the building where the tenants are.

In the CSV data the Device (meter number), Meter_group_name (tenant name), will always be the same unless a tenant changes, then the meter name will change and the meter number would be a key linked to tenant information. In the filename "location Name" would be good for creating the table name maybe, so each month the imported CSV file with that "location name" would add to the table with that "location Name"
Maybe the "report date generated-timestamp" could be written to the table also and if that is duplicate, do not write to table.

All 178 fields can be written to table row plus maybe the "location name" and " report date generated-timestamp and only needed data will be pulled from needed fields in a form.

I think this is probably getting too deep? Since it will be done by someone else I would like it to be a simple as possible. Emails come all at once and user imports first one and when successful it can be deleted and on to next emailed file import successful delete.

Thank you!

[datacaster]

Re: CSV or excel file importing help?

I have read through this thread over and over and I cannot wrap my head around the exact problem. I import data in CSV files extensively from many companies and there is no scenario yet that I haven't been able to make work. Like Al said, it might take upfront mapping work, but once done, it is done.

Paul,
Database strategy and solutions are a seriously detailed business. Logic and structure are critical and help will occur when you meticulously describe the structure and details of your situation and the objective. First things first.... get the data from spreadsheet to table. I don't understand why there is discussion about importing the CSV data to new tables? I must be missing something here. One table with a field that is a concatenation of building number and Meter Number should suffice as a unique account number and should be completely sufficient to filter records and all records reside in a single table. But truly getting the details of what the data import situation is needs better description...

...Each month I receive five different CSV spreadsheet containing data for electricity consumption.
...The 5 spreadsheets are electricity consumption for 5 different buildings, one spreadsheet for each building.
...Each building has multiple customers. Each customer has a unique meter having a unique meter number.
...In all spreadsheets, each row is a unique record of data collected by a unique meter that corresponds to electricity usage for a unique customer for the month.

>>> THE CRITICAL PIECES - WHICH ARE THEY IT? <<<<<
1: INTRASPREADSHEET CONSISTENCY: BUILDING LEVEL CONSISTENCY:
A - The spreadsheet for a building will always have the same field order - the columns in the spreadsheet are always the same and in the same order.
B - The spreadsheet for a building will NOT always have the same field order - the columns in the spreadsheet with vary month-to-month
2: INTERSPREADSHEET CONSISTENCY: CONSISTENCY BETWEEN SPREADSHEETS
A - The field order for each of the spreadsheet for the 5 spreadsheets ARE THE SAME for all spreadsheets.
B - The field order for each of the spreadsheet for the 5 spreadsheets are not the the same for all spreadsheets and do not match.

This sets the stage for understanding what you are facing. Until this is understood, we are all spinning in assumptions.

Hello Mike,
What you said here exactly describes the scenario that I have and might be the same meter company.
...Each month I receive five different CSV spreadsheet containing data for electricity consumption.
...The 5 spreadsheets are electricity consumption for 5 different buildings, one spreadsheet for each building.
...Each building has multiple customers. Each customer has a unique meter having a unique meter number.
...In all spreadsheets, each row is a unique record of data collected by a unique meter that corresponds to electricity usage for a unique customer for the month.

In answering your question for
1: INTRASPREADSHEET CONSISTENCY: BUILDING LEVEL CONSISTENCY:
A - The spreadsheet for a building will always have the same field order - the columns in the spreadsheet are always the same and in the same order.--------Yes the five CSV files are same field order and field names
B - The spreadsheet for a building will NOT always have the same field order - the columns in the spreadsheet with vary month-to-month -------NO
2: INTERSPREADSHEET CONSISTENCY: CONSISTENCY BETWEEN SPREADSHEETS
A - The field order for each of the spreadsheet for the 5 spreadsheets ARE THE SAME for all spreadsheets.--------Yes all 178 fields have the same field names and are in the same field order
B - The field order for each of the spreadsheet for the 5 spreadsheets are not the the same for all spreadsheets and do not match.--------NO each spreadsheet is identical other than file name which is building and date

There are other details about CSV files in reply today to Ted.

Thank you!
Paul