Excel - Combine Multiple CSV Files

  • Open a blank workbook
  • Select the Data menu
    • Select Get Data -> From File -> From Folder
    • Select "Browse..." and navigate to the folder containing the CSV files
    • Select "OK"
  • Select "Transform Data"
    • Use the filter in the 3rd column (Name) to deselect any files you do not want included in the dataset
    • Click the double arrow icon (Combine Files) on the right of the 2nd column (Content)
    • Select "OK"
  • Select the "Source.Name" column
    • Select "Remove Columns" from the Ribbon
  • Ensure the first column of data is highlighted
    • Select Remove Rows -> Remove Blank Rows from the Ribbon
  • Select "Close & Load" from the Ribbon
You can now save the combined files as a CSV file.

Comments

Post a Comment

Popular posts from this blog

Linux: Permanent CIFS/SMB mount

I needed to permanently mount a directory on a Windows server that had spaces in the path. I also wanted to use a credentials file so that the remote server username and password would not be in the world readable fstab file. The following was cobbled together from: Mount samba shares with utf8 encoding using cifs and: Mount password protected network folders Create a credentials file (as root) containing the username and password and restrict it's ability to be read by other users (/etc/fstab is world readable). vi /root/.smbcredentials username=someusername password=somepassword Save and exit the file. Change the file permissions to only be readable by root. chmod 600 /root/.smbcredentials Update the fstab file Add the following line to /etc/fstab (spaces are replaced with \040  or the mount will fail) // ip address / c$/Program\040Files/Microsoft\040SQL\040Server/ /var/backups/Server cifs credentials=/root/.smbcredentials,iocharset=utf8,sec=ntlm 0 0 Exit and s
Read more

Plesk - Decrypt FTP Passwords

I was working on a project where Plesk user details were being imported into a database and it would help to know the password. Since Plesk 11 the Plesk Client account is hashed which makes it unrecoverable, but other passwords are encrypted with 128 bit AES encryption and a salt. If the FTP login name is the same as the client login there is a chance the customer was lazy and used the same details. To decrypt the passwords you will need the server secret key and the password string from the plesk database. The server secret key can be retrieved with the following command on a linux server: cat /etc/psa/private/secret_key | base64 Matching FTP logins (and their encrypted password) can be retrieved from the Plesk database with: SELECT REPLACE(s.home, '/var/www/vhosts/', '') AS domain, c.login, a.password FROM clients c LEFT JOIN sys_users s ON s.login = c.login LEFT JOIN accounts a on s.account_id = a.id; The results will look like: +-------------------
Read more

IPTables: Clear all rules

When you need to clear all rules and set the default police to ACCEPT. iptables-save | sed "/-/d;/^#/d;s/DROP/ACCEPT/" | iptables-restore This is not permanent. The default rules will be reloaded if IPTables, your firewall or the server is restarted.
Read more