Importing Load Profiles

[DallasRead]

I have been struggling to import load profiles into Premium edition.

 

My task is to import Half Hourly usage data for a site into PVSol, then I can run a simulation to predict the Exported energy and onsite usage, important to show the business case for a local Power Purchase Agreement.

 

My problem is that no matter what format I try (text or CSV in every combination that Excel can output), I cannot get the data to load into the 'Import Load Profiles' section of the 'Consumption' page.

 

The Error message is 'konvertierungsfehler in datensatz in zeile 1',

or 'conversion errors in data in line 1' translated.

 

I have even tried to word wrap the text file to try to get it into a single column then save it without luck

I have tried formatting the column to ensure there are 2 decimal places, so it mimics the 'number format file' drop down list (####.##) exactly, but no.

 

I believe it is a format issue, but I really cannot work out what it is.

 

I have included a couple of the files I have tried on this posting.

 

Can anyone help?

 

Dallas

Simple HHM for PVSol (MSDOS).csv

Test (Unicode Text)(WRAP).txt

[hotline_sf]

Hello DallasRead,

 

The data in the file don't have the right format. You have 365 rows representing the days and in the rows you have the values for the half hours.

 

But the import in PV*SOL requires that every values has to be in a single row.

 

So you should have one column with 17520 rows.

 

It should look like this:

 

965.8

928.2

914

920.5

...

 

Best regards

 

Your Technical Support

[DallasRead]

Thanks for your response on this.  I thought it must be a formatting problem.

Unfortunately all our Half hourly meter data is in tabular form in UK, so it is simple to remove the columns and rows to remove the header, but difficult to convert to a single column of 17,520 rows.

 

Is there any way in which we can enter the data in a 48 Column x 365 Row format?

 

Alternatively have you a spreadsheet with a suitable macro to convert the above format to the required single column.  It should be just maths, but I'm not sure I have the required Excel skills!

 

Cheers

Dallas 

[Aztec_Oli]

Hello DallasRead,

 

The data in the file don't have the right format. You have 365 rows representing the days and in the rows you have the values for the half hours.

 

But the import in PV*SOL requires that every values has to be in a single row.

 

So you should have one column with 17520 rows.

 

It should look like this:

 

965.8

928.2

914

920.5

...

 

Best regards

 

Your Technical Support

Hello Technical Support,

 

I have a file with all the data in one column but I am getting the error message " Keine Leseberechtigung der Datei"

As far as I can tell there should be no issue with permissions for the file.

Can you help?

Oliver

[Aztec_Oli]

Thanks for your response on this.  I thought it must be a formatting problem.

Unfortunately all our Half hourly meter data is in tabular form in UK, so it is simple to remove the columns and rows to remove the header, but difficult to convert to a single column of 17,520 rows.

 

Is there any way in which we can enter the data in a 48 Column x 365 Row format?

 

Alternatively have you a spreadsheet with a suitable macro to convert the above format to the required single column.  It should be just maths, but I'm not sure I have the required Excel skills!

 

Cheers

Dallas 

I used the transpose function to get the data into a single column

[DallasRead]

I thought that might work, but it is my understanding that the transponse only works if you have one row to convert to one column.

In this case I have 365 rows (the days) and 48 columns (one for each 30min usage period) so the transpose doesn't work, as far as I know.

I am more than happy to be proven wrong though!

[hotline_sf]

Hello,

 

@Aztec_Oli

The message "Keine Leseberechtigung der Datei" appears if the file is opened in another program, e.g. Excel, at the time of import. Please close the file in the other program and retry the import.

 

@DallasRead

Unfortunately we don't have a macro for the conversion to one column.

 

Your Technical Support

[Aztec_Oli]

I thought that might work, but it is my understanding that the transponse only works if you have one row to convert to one column.

In this case I have 365 rows (the days) and 48 columns (one for each 30min usage period) so the transpose doesn't work, as far as I know.

I am more than happy to be proven wrong though!

make a good cup of coffee is all i can suggest! Sadly that is what I had to do.

I firstly set up a column to number each day, so 48 no. 1s then 48 no. 2s etc then copy and pasted the transpose function down changing the array for each day using the first coloumn as a reference. 

I am almost certain there is a better way to do this, but it hasn't come to me yet.

Sorry I don't have the magic solution! yet.

[Aztec_Oli]

Hello,

 

@Aztec_Oli

The message "Keine Leseberechtigung der Datei" appears if the file is opened in another program, e.g. Excel, at the time of import. Please close the file in the other program and retry the import.

 

@DallasRead

Unfortunately we don't have a macro for the conversion to one column.

 

Your Technical Support

Thank you - a simple fix!

[DallasRead]

I will try to write a suitable macro myself to do this.  If I achieve this I'll post is on the forum to share.

 

Dallas

[hotline_sf]

Hello DallasRead,

 

You can try the following macro:

 

______

 

Sub Profile()

 

Dim rowZ As Long

Dim strPartstring() As String

Dim strDelimiter As String

 

'Parameter definition

Set curws = ThisWorkbook.Sheets("Table") 'Enter the name of your worksheet instead of "table"

rowZ = 1 'starting row

strDelimiter = "," 'Definition of the delimeter e.g. comma, semicolon, hyphen, etc.

 

'Add a new worksheet

Sheets.Add

ActiveSheet.Name = "Table2"

 

'Setting all data lines

For x = 1 To 365

 

'Reading of the Strings

strPartstring = Split(Trim(curws.Cells(x, 1).Value), strDelimiter)

 

For a = LBound(strPartstring) To UBound(strPartstring)

 

'Writing of the data in the new worksheet

Worksheets("Table2").Cells(rowZ, 1).Value = Trim(strPartstring(a))

 

rowZ = rowZ + 1

 

Next a

 

Next x

 

End Sub

__________

 

You just have to change the name of the worksheet "table" into your worksheets name. Or you change the name of your worksheet into Table.

 

We tried it with your file and it worked very well. Attached you can find the result.

 

Your Technical Support

Result_macro.csv

[DallasRead]

Thank you for the conversion!  

I have tried to use your macro to convert my table by copying and pasting that into my existing spreadsheet (changing the table name to 'table') and running the macro, but unfortunately it only copies the first column of 365 numbers, and does not copy the second column below that, so not sure why that is not working?

 

Can you post my original excel spreadsheet with the macro embedded, then I can simply duplicate it, and use in the future with new data?

 

This 365 Row x 48 Column format is the standard output to customers in the UK with a >=100kVA supply. There is normally other rows & columns included such as the headers, dates and often other extra info such as power factor lead/lag etc, but those are easily deleted without disturbing the main usage data.

 

I'm sure it would be useful if it was included within the PVSol programme ongoing.

 

Many thanks so far though

Dallas 

[hotline_sf]

Hello Dallas,

 

For safety reaseons we don't allow the upload of spreedsheets with macro in the forum.

 

Maybe it helps if I describe the procedure.

 

I opened your file "Simple HHM for PVSol (MSDOS).csv" and renamed the worksheet name into table. Afterwards I have opened the vba editor (ALT+F11). Then I have selected table on the tree view and copied the code into the editor (see attached screenshot, sorry but I only have it in German). Now I left the vba editor and ran the macro.

 

Your Technical Support

[Pastyl89]

I have been experiencing the same problem trying to import load profiles. 

 

Irrespective of the format I get the 'conversion errors in data in line 1' message every time.

 

The data I have is on a 20-minute interval. Beacuse such option is not available in PVSol, I reduce the number of values to17520 so that it fits to the 30-minute format.

 

I checked every other possible cause like format,number of values etc. 

 

Files attached.

 

Please help

 

 

 

 

 

values.txt

load profile EAC789070.CSV

[hotline_sf]

Dear Pastyl89,

 

Please check your file. There are some entries in coulmn B, e.g. in row 5912. If you delete column B it will work.

 

 

Best regards

 

Your Technical Support

[Pastyl89]

Thank you very much.

 

Now it works perfectly.

 

Regards

[John Lorenz]

I have this problem of viewing/editing the load profile using PV Sol 2020(R8). The version of the PV Sol I used for creating that load profile was version 2019 but when I updated into PV Sol 2020, I cant view/edit the load profile. Can you help me with this?

[developer_mh]

Hi John,

we re-developed, updated and unified the load profile features in version 2020, so unfortunately you can't edit some of the older load profiles from version 2019 or before. This applies to individual appliances and load profiles from day profiles. You can simulate those profiles without problems, however.

Hope that helps, kind regards,

Martin

[MrKinch]

On 7/23/2015 at 2:12 PM, DallasRead said:

Thank you for the conversion!  

I have tried to use your macro to convert my table by copying and pasting that into my existing spreadsheet (changing the table name to 'table') and running the macro, but unfortunately it only copies the first column of 365 numbers, and does not copy the second column below that, so not sure why that is not working?

 

Can you post my original excel spreadsheet with the macro embedded, then I can simply duplicate it, and use in the future with new data?

 

This 365 Row x 48 Column format is the standard output to customers in the UK with a >=100kVA supply. There is normally other rows & columns included such as the headers, dates and often other extra info such as power factor lead/lag etc, but those are easily deleted without disturbing the main usage data.

 

I'm sure it would be useful if it was included within the PVSol programme ongoing.

 

Many thanks so far though

Dallas 

 

Hey there,

Understand that this topic was made a long time ago but I'm having the same issue as this chap.

I've tried using a range of methods like transpose, concancate and making a new macro with ChatGPT's help.

Is there any solution to this?

Kind regards,

James

 

[MrKinch]

33 minutes ago, MrKinch said:

 

Hey there,

Understand that this topic was made a long time ago but I'm having the same issue as this chap.

I've tried using a range of methods like transpose, concancate and making a new macro with ChatGPT's help.

Is there any solution to this?

Kind regards,

James

 

Following this, I have found that the software that we use allows it to be imported via a different method.

Each row of data can be condensed into a single cell. The formula is then repeated down for all 365 rows. This can then be imported.

=CONCATENATE(A1,",",B1,",",C1,",",D1,",",E1,",",F1,",",G1,",",H1,",",I1,",",J1,",",K1,",",L1,",",M1,",",N1,",",O1,",",P1,",",Q1,",",R1,",",S1,",",T1,",",U1,",",V1,",",W1,",",X1,",",Y1,",",Z1,",",AA1,",",AB1,",",AC1,",",AD1,",",AE1,",",AF1,",",AG1,",",AH1,",",AI1,",",AJ1,",",AK1,",",AL1,",",AM1,",",AN1,",",AO1,",",AP1,",",AQ1,",",AR1,",",AS1,",",AT1,",",AU1,",",AV1)

This may not help everyone but it has worked for us.

Kind regards,

James

[PhilR]

All the data I have received from clients comes in spreadsheets with one row of 48 entries per day. I imagine this is a fairly common format. That a £1000 plus piece of software is unable to accept a CSV input in a common format it appalling. To have to manipulate 365 lines of data by hand is insulting to PV SOL clients. This issue has been around for some years looking at the forum above, and still PV SOL has done nothing?

[developer_fw]

Hello PhilR

Thank you for your input!

It would be great to have a standardised format when it comes to time series data like load profiles, measured values, but I'm afraid there is none. There is even many database structures when looking at databases specialised in handling time series data.
Since there are uncountable ways of storing information, we did not put effort in creating a second Micro$oft Excel or LibreOffice Calc to manipulate input data.

You could send me your data so we could work out a solution. Maybe it is possible to create a Table once and reuse it for future input data?

I am happy to help.
Kind regards
Frederik

[PhilR]

On 5/8/2023 at 9:27 AM, developer_fw said:

Hello PhilR

Thank you for your input!

It would be great to have a standardised format when it comes to time series data like load profiles, measured values, but I'm afraid there is none. There is even many database structures when looking at databases specialised in handling time series data.
Since there are uncountable ways of storing information, we did not put effort in creating a second Micro$oft Excel or LibreOffice Calc to manipulate input data.

You could send me your data so we could work out a solution. Maybe it is possible to create a Table once and reuse it for future input data?

I am happy to help.
Kind regards
Frederik

The data  I regularily receive from clients is in spreadsheet format - ie xls or CVS and has one row per day, usually kWh consumed every 30 minutes, so that is 48 columns of kWh data for 365 rows

This seems to be common format

To convert to one row per entry is a massive task

If there was some way that PV Sol could accept data structured in this way, it would make it so much more practical

Phil

[developer_fw]

Hello PhilR

OK, I thought you did have something more exclusive and misread your first post. PV*SOL does support your data format for a long time.

I hope this helps.

Kind regards
Frederik

[PhilR]

Day by Line 2000027387248.csvHi Frederick

I have tried this option, but cannot get it to import, always file corruption errors

When I lay out same data in one line per entry ( one line per 30 minutes) it imports perfectly

I attach the file and perhaps you would  not mind taking a look to see whats the problem please

I must be doing something stupid?