Email with csv attachment. Open with Excel

General
1

Hey everybody, i know that there are already quite similar topics discussing this issue. But non of the solutions there worked for me.
I'm creating a csv file to store an order list and then sending this file per Email. If I open the file with Excel, every entry is in the first column (A1, B1, etc.). But i would like to have them in different columns (A1,A2,A3 and B1,B2,B3 and so on).

My flow:

[{"id":"1e5090de.8827af","type":"tab","label":"Email CSV File","disabled":false,"info":""},{"id":"c56fd571.949928","type":"file","z":"1e5090de.8827af","name":"","filename":"/home/pi/Nachbestellen.csv","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":760,"y":120,"wires":[[]]},{"id":"e55a2b72.801de8","type":"inject","z":"1e5090de.8827af","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":120,"wires":[["d2689be4.e882e8"]]},{"id":"d2689be4.e882e8","type":"function","z":"1e5090de.8827af","name":"Column names","func":"msg.payload =['ID','Beschreibung','Menge Pro Kasten','Lieferant','Anzahl pro Bestellnr.','Bestellnummer','Wert','Einheit']\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":370,"y":120,"wires":[["91b7ff4d.78f0e"]]},{"id":"91b7ff4d.78f0e","type":"csv","z":"1e5090de.8827af","name":"","sep":",","hdrin":"","hdrout":"none","multi":"one","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":550,"y":120,"wires":[["c56fd571.949928"]]},{"id":"90ce83a6.92bf","type":"e-mail","z":"1e5090de.8827af","server":"outlook.office365.com","port":"587","secure":false,"tls":false,"name":"example.email@gmail.com","dname":"","x":1100,"y":360,"wires":[]},{"id":"2f7da25c.da3eae","type":"change","z":"1e5090de.8827af","name":"CSV Datei auswählen","rules":[{"t":"set","p":"attachments","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":680,"y":300,"wires":[["90ce83a6.92bf"]]},{"id":"f2e7989a.b6e5c8","type":"inject","z":"1e5090de.8827af","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":300,"wires":[["e77fd275.e38cb"]]},{"id":"e77fd275.e38cb","type":"file in","z":"1e5090de.8827af","name":"","filename":"/home/pi/Nachbestellen.csv","format":"utf8","chunk":false,"sendError":false,"encoding":"utf8","x":420,"y":300,"wires":[["2f7da25c.da3eae"]]},{"id":"1420dc04.5d1754","type":"function","z":"1e5090de.8827af","name":"Email settings","func":"msg.topic = 'Nachbestellung';\nmsg.payload = 'Datei befindet sich im Anhang';\nmsg.attachments = [{filename:'Nachbestellen.csv', path:'/home/pi/Nachbestellen.csv'}];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":420,"wires":[["90ce83a6.92bf"]]},{"id":"71db4105.96927","type":"inject","z":"1e5090de.8827af","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":180,"y":420,"wires":[["1420dc04.5d1754"]]},{"id":"3b48ecb0.1dd564","type":"comment","z":"1e5090de.8827af","name":"Create file and set column names","info":"","x":220,"y":60,"wires":[]},{"id":"929d13b2.4ab32","type":"comment","z":"1e5090de.8827af","name":"Sent Email with attachments. Option 1","info":"","x":260,"y":240,"wires":[]},{"id":"fec19f2f.9b8e1","type":"comment","z":"1e5090de.8827af","name":"Sent Email with attachments. Option 2","info":"","x":250,"y":380,"wires":[]}]

I hope someone can help me.

Best regards.

2

Can you please post a sample of the CSV?
If you natively open the CSV in Excel it reads a line into a row. To import a CSV you need to select "Data -> Import from File" and then select the csv. Then you can select the delimiter of the CSV (most common in my region is a ";") and then you also need to check the format. NodeRED posts "." as decimal delimiter. In some regions there is "," as delimiter (e.g. Germany).

But for me it looks like a handling issue of excel and not node RED.

I hope this points to the right direction.

3

@lostbits Thank you for your answer.
You are right. It's a handling issue of excel. If i import the csv file like you said it works just fine.
Do you know if there is a way to create that .xlsx file with Node-Red? Or do need to import the CSV file every Time?

4

uhhh I thought about doing it ... but never did. Maybe you should save a file with Excel as a readable format ... then you can try to reproduce by node JS in a function... would be a nice block!

5 
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>X</Author>
  <LastAuthor>X</LastAuthor>
  <Created>2021-06-17T09:39:05Z</Created>
  <LastSaved>2021-06-17T09:40:36Z</LastSaved>
  <Company>X</Company>
  <Version>16.00</Version>
 </DocumentProperties>
 <CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Enabled dt:dt="string">true</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Enabled>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_SetDate dt:dt="string">2021-06-17T09:41:10Z</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_SetDate>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Method dt:dt="string">Standard</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Method>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Name dt:dt="string">924dbb1d-991d-4bbd-aad5-33bac1d8ffaf</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_Name>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_SiteId dt:dt="string">9652d7c2-1ccf-4940-8151-4a92bd474ed0</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_SiteId>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_ActionId dt:dt="string">87661ee6-3d54-4b7e-9052-59206a2675a4</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_ActionId>
  <MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_ContentBits dt:dt="string">1</MSIP_Label_924dbb1d-991d-4bbd-aad5-33bac1d8ffaf_ContentBits>
 </CustomDocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>12300</WindowHeight>
  <WindowWidth>28800</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Tabelle1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
   <Row>
    <Cell><Data ss:Type="String">R1C1</Data></Cell>
    <Cell><Data ss:Type="String">R1C2</Data></Cell>
    <Cell><Data ss:Type="String">R1C3</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">R2C1</Data></Cell>
    <Cell><Data ss:Type="String">R2C2</Data></Cell>
    <Cell><Data ss:Type="String">R2C3</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3" x:Data="&amp;L&amp;&quot;CorpoS&quot;&amp;10&amp;M007a93Daimler Internal&amp;1#"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.78740157499999996" x:Left="0.7" x:Right="0.7"
     x:Top="0.78740157499999996"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>599</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>5</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

If you look at this minimal excel doc ... you find what you need

(you can manage to get this doc by saving a sheet in excel as "XML configuration table 2003 (xml)".

if your problem is solved, please mark the "solving" answer as solution. Thx.

7

@lostbits Alright. Thanks again for your help. :slight_smile:

1 Like
8

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.