Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

CSV or excel file importing help?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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
    .

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

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


        • #5
          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
          Last edited by datacaster; 12-18-2019, 04:30 PM.

          Comment


          • #6
            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
            .

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

            Comment


            • #7
              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

              Comment


              • #8
                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.

                Comment


                • #9
                  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
                  .

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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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
                      .

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

                      Comment


                      • #12
                        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"

                        Comment


                        • #13
                          Re: CSV or excel file importing help?

                          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

                          Comment


                          • #14
                            Re: CSV or excel file importing help?

                            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!

                            Comment


                            • #15
                              Re: CSV or excel file importing help?

                              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

                              Comment

                              Working...
                              X