Jump to content

DallasRead
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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

Profile.jpg

Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

  • 4 years later...

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...