Copy a whole excel file to one cell in a different file

[mgunia]

I'd like to say "hello" to everyone as it is my first post. I looked for the solution to my problem on the internet for over 2 hours and I didn't find anything relevant. I hope you are able to help me.

I have a bunch of invoices written in the excel files (CSV format). What I need to do is to copy a whole invoice and paste it into another new file. The whole invoice needs to be placed in the one cell. Then another needs to be copied the same way and copied to the cell below the first invoice. I know how to copy the invoices but my problem is I can not copy the whole invoice into one specific cell. Could anyone help me with this?

[hkwhitten]

CSV format means that cell contents are separated by commas. If you open the file with Excel, it will put each value in a separate cell. To put all the data into a single cell, you need to open the csv file in a text editor (like notepad), select all the text in the file (edit-> select all), copy the selected text (edit-> copy). Now you have the csv file contents in your clipboard. You can then open a new worksheet with excel, click on the cell you want to paste the information into, then go up to the formula area (the one with the fx button - location differs depending on your version of excel) and click there, and then either select edit->paste from the excel menu, or just type ctl-v to paste - if you try to paste directly into the cell it won't work - each line in the csv file will be in a different cell

So that answers your question (assuming invoice csv files are less than 32K characters each, the max a cell can hold).

I'm not sure it will solve your problem, though. What do you want to do once you have each invoice's text in its own cell? There's probably a better way. For example, do you want to sum over a set of invoice totals?

Good luck,
HKW

[mgunia]

Thank you hkwhitten for your reply. Unfortunately, it doesn't solve my problem. Of course, I know I can copy the text this way. The problem is - this is not suitable for me. The number of invoices is too big and it is being issued every month. Therefore, I need this process to be automated.

Why do I need to do this? I will upload these invoices to the company database (Salesforce) using Apex Dataloader. Upload process requires to have every data in columns. I will need 3 columns: "Invoice name", "Invoice date" and "Invoice text" - so I need to copy every invoice into the 3rd column called invoice text. I can have these invoices in 2 formats - xls or csv. Maybe standard Excel format will help here?

[TrustyTony]

So what kind of errors/wrong output are you getting from xlrd/xlwt and what is your code causing those errors?

[mgunia]

to be honest, I was trying to do this using standard python modules. I read about xlrd/xlwt on different websites...seems like this thing might be very helpful but I have problems to understand how should I properly install this module to work for me. I know it might sound funny or pathetic to some of you but please be aware I am new to scripting in python (and scripting at all). I donloaded xlrd and tried to sort it out...but I failed to implement it. Perhaps you would be able to explain it to me clearly how should I handle xlrd to work properly?

[TrustyTony]

Read the Gribouillis' description of the installation process from thread I linked to! Drop sudo if you are using Windows.

[mgunia]

Great pYTony! Your installation guide provieded in the topic you linked helped me to install xlrd (i'm affraid withouth this guide I couldn't do this). Therefore, I am really thankful for this.

I see this is quite complex tool. What command should I use to be able to grab the whole file and copy this output to 1 cell in another file? And...is xlrd enough for me to do this or I also need to instal xlwt?

[hkwhitten]

"Why do I need to do this? I will upload these invoices to the company database (Salesforce) using Apex Dataloader. Upload process requires to have every data in columns. I will need 3 columns: "Invoice name", "Invoice date" and "Invoice text" - so I need to copy every invoice into the 3rd column called invoice text. I can have these invoices in 2 formats - xls or csv. Maybe standard Excel format will help here?"

mgunia, can you go back to the person who asked you to do this, and ask some clarifying questions? It could save you a lot of wasted time and effort. Here are the basic issues - and you may already know the answers but the answers are not clear to me from your comments so far.

First, you need more detail around the Apex input format. If you load the three fields you list, "Invoice name," "Invoice date," and "Invoice text" you will lose the line item detail structure of the invoice. If I were doing this I would ask the person who assigned the task or the user of the information in Sales Force (whichever is appropriate) what the invoice data will be used for. That will give you a better idea of what you need to do with the excel data. an invoice text field with multiple line items probably won't be very useful. It is likely you need a flat file out of the excel csv files that looks something like:

Customer ID, Invoice ID, Date, line#, Product, Quantity, Price

You need Customer ID (or something like it, maybe 'Account') to map the invoice to the customer in Salesforce. Invoice ID ("name?") and date are typical invoice header fields. There may be others available. Remaining fields provide line item detail, possibly more than one line per invoice. Again, there are likely more fields available than these, and the names are just typical of sales order entry / invoicing systems.

Once you have an agreed upon format that addresses the business needs, you can use xlrd to format the csv files into that agreed on flat file format. At that point, you are out of excel and "cell" is not a meaningful concept - you just have a file with records with fields.

I hope this explanation helps you. It's often the case that developers are uncomfortable asking questions, thinking it is a sign of weakness. In fact, you will discover that the more proficient a developer is, the more questions he asks, and the happier his customers are that he did ask.

[mgunia]

Hi Khwhitten,

In the company I am one of the people who is importing data via Apex Dataloader. This software requires to have a column with the proper title and data below it, to let you import it to Salesforce. Each column corresponds to 1 field in Salesforce. The file needs to be CSV. Example….

Company name Territory Status
Abc US Client
Def EU Prospect
Ghi US Prospect

Previously, we were making mass data imports manually which for occasional imports works good. However, we want to store or invoices in Salesforce – according to them we would be able to build reports. The fact a process of issuing invoices is repeatable and we have lots of them, we need to automate the data input to Salesforce.

The fields in Salesforce are pre-defined similar to excel (you can set up fields like currency/date/numbers or text. The “Invoice text” field is a text box where you can type any text just like in the text box here in this forum. If I take manually copy the invoice and paste it to the text box of the “Invoice text” field it copies everything correct. But as I mentioned above – it is not possible to make it manually. Therefore we need to fulfil the requirements of Apex Dataloader and get the whole invoice text into 1 cell. I know it is possible in excel (e.g copy some random portion of text, open excel, type anything at the beginning in the cell and then try to past the text you copied before – then excel will put everything into 1 column…no matter what formatting you were having in your copied text).

So I was thinking that it could be possible to “force” Excel to fit the whole text in the 1 cell…or maybe there is some kind of trick which I should do before to let Excel treat my invoice text as a one solid portion of text which should be pasted in 1 cell?

[TrustyTony]

Why do not only use csv, instead of dealing with Excel? Oh, the originals are in Excel. But you could just save it in file as CSV.

[mgunia]

well there is no need of dealing with Excel. The only thing what I need is a CSV file with columns where 1st rows are titles and the rest below are exact data. As I stated before my programming knowledge is limited therefore that was the only way which came to my mind. If there is some kind of other method I would be really happy if you can share your knowledge and explain me the other method

[hkwhitten]

Sub Macro1()
' Macro1 Macro
' Macro recorded 5/23/2012 by hkw
'
'   Documentation

'   All invoice csv files to be processed go in c:\invoices\
'   You can put this spreadsheet there too - or anywhere.
'   Resulting csv file will be written to c:\invoices\out\apex.csv
'   (apex.csv file is overwritten each run)
'   Once the inputs are in the invoices directory,
'   You open this excel file (apexinputmaker) and run macro1
'   (or set the macro up to autorun and just double click the file)

Dim fn, Celldata As String
Dim rownum As Integer ' rows in output file - row 1 is heading row.
Dim csvcols, csvrows, i, j As Long
ChDir "C:\invoices\"

' fn is file name
'
    fn = Dir("C:\invoices\*.csv")
    rownum = 1
    If fn = "" Then
        MsgBox ("no invoice files in invoices directory")
        Stop
    End If
' main loop: iterate on csv invoice files in invoices directory
    While fn <> ""
        Workbooks.Open Filename:=fn
'       Worksheets(fn).Activate
        rownum = rownum + 1                             'next row for output
'
' Figure out how many cells (rows and columns) the PO csv file uses
'
        ActiveCell.SpecialCells(xlLastCell).Select
        Range(Selection, Cells(1)).Select
        csvcols = Selection.Columns.Count
        csvrows = Selection.Rows.Count
'           Loop through cells in invoice csv file
'           and paste each value to the end of the current output cell
'           If you have very large invoices this may break.
        Windows("apexInputMaker.xls").Activate
' Write the first two fields (Invoice name and Invoice date) here (this is an example based  on my dummy data)
        Sheets("output").Select
        Cells(rownum, 1).Select
        ActiveCell.FormulaR1C1 = Workbooks(fn).Sheets(1).Cells(3, 2)             ' Invoice name
        Cells(rownum, 2).Select
        ActiveCell.Value = Workbooks(fn).Sheets(1).Cells(5, 2)             ' Invoice date
        ' format dates...
        Columns("B:B").Select
        Selection.NumberFormat = "m/d/yyyy"
        Cells(rownum, 3).Select
' now build up the Invoice text field from individual cells... could use a little more formatting
        For i = 1 To csvrows
            For j = 1 To csvcols
                Celldata = Workbooks(fn).Sheets(1).Cells(i, j) & "  "
                ActiveCell.Value = ActiveCell.Value & Celldata
            Next j
            ActiveCell.Value = ActiveCell.Value & Chr(10)
        Next i
'
        Workbooks(fn).Close
        fn = Dir()
    Wend
' Now save the output as csv file in c:\invoices\out
ChDir "C:\invoices\out"
fn = Dir("C:\invoices\out\apexfile.csv")
If fn <> "" Then
    Kill fn
End If

ActiveWorkbook.SaveAs Filename:="C:\invoices\out\apexfile.csv", FileFormat _
        :=xlCSV, CreateBackup:=False

End Sub

[hkwhitten]

Well, I kind of jumped the gun on that last post. Here's the prefatory note:

The good thing about using Excel is it has some csv smarts - checks for things like imbedded commas and quotes.
Here's an excel macro to do what you're looking for (I think) - I had to make some assumptions about the location of the first two fields (look at the comments). If you paste this macro into a spreadsheet and put all your invoice .csv files in the specified location, it will produce a csv file of all the invoices. Or better yet download the attached zip file w/ test cases and output example. ... upload not working for me. I will try to email the zip folder.

[mgunia]

I'd like to thank all of you for your help. The macro written by hkwhitten helped me to solve the problem. Thanks guys!