New call-to-action
Results 1 to 23 of 23

Thread: CSV or excel file importing help?

  1. #1
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default CSV or excel file importing help?

    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

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,494

    Default 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.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,739

    Default Re: CSV or excel file importing help?

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

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,665

    Default 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.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default 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!
    Attached Files Attached Files
    Last edited by datacaster; 12-18-2019 at 03:30 PM.

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,494

    Default 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.,
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default 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

  8. #8
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,665

    Default 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.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  9. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,494

    Default 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.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  10. #10
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,739

    Default Re: CSV or excel file importing help?

    what are the descriptions of the columns?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  11. #11
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,494

    Default Re: CSV or excel file importing help?

    The string at the beginning is the field content/types Robin.
    178 fields.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  12. #12
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,229

    Default 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.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  13. #13
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,739

    Default Re: CSV or excel file importing help?

    Quote Originally Posted by Ted Giles View Post
    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
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  14. #14
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default Re: CSV or excel file importing help?

    Quote Originally Posted by Ted Giles View Post
    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!

  15. #15
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default Re: CSV or excel file importing help?

    Quote Originally Posted by Mike Wilson View Post
    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

  16. #16
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,229

    Default Re: CSV or excel file importing help?

    Hi Paul,
    Attached is an example of how I might go about the task you have described. Put your new CSV spreadsheets into the "New" folder and start the import. I made the table with field names from the spreadsheet stripped of all the extra special characters and made then close to what they are in the spreadsheet. However it doesn't matter what they are in the spreadsheet it just reads them all in order and writes them in order. There is a placeholder txt file in the Archive folder. An empty folder will not get zipped so I put that in there to get the Archive folder in the zip file. Hope this helps.
    Attached Files Attached Files
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  17. #17
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default Re: CSV or excel file importing help?

    Quote Originally Posted by Mike Wilson View Post
    Hi Paul,
    Attached is an example of how I might go about the task you have described. Put your new CSV spreadsheets into the "New" folder and start the import. I made the table with field names from the spreadsheet stripped of all the extra special characters and made then close to what they are in the spreadsheet. However it doesn't matter what they are in the spreadsheet it just reads them all in order and writes them in order. There is a placeholder txt file in the Archive folder. An empty folder will not get zipped so I put that in there to get the Archive folder in the zip file. Hope this helps.
    Thanks you Mike for the Christmas present, It's great!
    Excellent excellent work !

    I just finished installing the meters at the locations, so I should get some real data on January 1st. In the meantime I'll play with what I have and build from there the full project (I hope?).
    I was looking the code over and some code I had similar, but was missing way to much and of course mine didn't work, as I am not a programmer.
    I am retiring early in 6 months (I hope) and want to learn this to keep my mind sharp, but I have some personal issues now with health that might make it very hard for me to pursue as planned. Hard to teach an old dog new tricks, but I'll keep trying!
    Thank you so much for helping me out!
    Thank you everyone else too for your assistance in this, it is really truly appreciated and I appreciate all the help in the community that help people like me that just don't always get it, but want to learn somehow!


    Thanks!
    Paul

  18. #18
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,739

    Default Re: CSV or excel file importing help?

    Hi Mike,
    Is there a reason the Mainmenu cannot be put in design mode to see your scripts?
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  19. #19
    Member
    Real Name
    Jon P Moody
    Join Date
    Nov 2016
    Posts
    208

    Default Re: CSV or excel file importing help?

    Quote Originally Posted by MoGrace View Post
    Hi Mike,
    Is there a reason the Mainmenu cannot be put in design mode to see your scripts?
    Works for me!

  20. #20
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default Re: CSV or excel file importing help?

    Hi Robin,
    open developer first and then open workspace folder where you unzipped file or it will launch the Alpha runtime if you double click the .adb file

    Paul

  21. #21
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,739

    Default Re: CSV or excel file importing help?

    Thanks Paul, I didn't know that...
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  22. #22
    Member
    Real Name
    Paul Verboom
    Join Date
    Apr 2006
    Location
    Halifax, Nova Scotia, Canada
    Posts
    144

    Default Re: CSV or excel file importing help?

    The following post newly added to the Code Archive may be of use if you can figure it out.

    https://forum.alphasoftware.com/show...ne-for-Desktop

  23. #23
    Member
    Real Name
    Paul Hunter
    Join Date
    Aug 2014
    Posts
    87

    Default Re: CSV or excel file importing help?

    Thank you for the posting. I will have some fun playing with this one.
    I really appreciate the input and this code to learn from.

    Thanks Paul!!!

Similar Threads

  1. Importing an Excel file
    By citigirl in forum Mobile & Browser Applications
    Replies: 5
    Last Post: 03-21-2016, 06:21 PM
  2. Some records have blank characters when importing from Excel 2010 file
    By joobeng in forum Alpha Five Version 10 - Desktop Applications
    Replies: 0
    Last Post: 08-21-2013, 12:11 PM
  3. Difficulty Importing Excel File
    By Laikisha J. in forum Alpha Five Version 7
    Replies: 28
    Last Post: 01-24-2006, 03:17 PM
  4. importing excel file and save mode
    By Anoop Sood in forum Alpha Five Version 5
    Replies: 3
    Last Post: 03-21-2004, 06:49 AM
  5. importing from Excel
    By lkatz1 in forum Alpha Five Version 5
    Replies: 4
    Last Post: 01-05-2004, 09:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •