Monthly Archives: July 2014
1
Doing an export to InfusionSoft from MSSQL? Here’s How
So we had to export customers from our MSSQL Database to InfusionSoft Contacts CSV. This query will help you escape the crazy commas and ensure integrity of your resultant .csv file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT distinct dbo.Customers.FirstName, coalesce(dbo.Customers.Middle,'') as MiddleName, dbo.Customers.LastName, '' AS Title, '' AS suffix, '' AS company, coalesce('"'+ Replace(left(Customers.referrer,100), ',','') +'"','""') AS [Lead Source], '' AS [job title], '"'+cast(dbo.Customers.idCustomer AS nvarchar ) +', Imported"' AS [Contact Notes], '"'+customers.interest+', ' + 'New Lead"' AS Tags, dbo.Customers.EmailAddress AS email, '' AS website, coalesce(dbo.Customers.Phone,'') AS [phone 1], '' AS [phone 2], '' AS fax1, dbo.Customers.Address AS [Street Address 1], dbo.Customers.Address2 AS [Street Address 2], dbo.Customers.City, dbo.Customers.State, dbo.Customers.ZipCode AS postalcode, '' AS zipFour1, dbo.Customers.Country FROM dbo.Orders INNER JOIN dbo.Payments ON dbo.Orders.idOrder = dbo.Payments.idOrder RIGHT OUTER JOIN dbo.Customers ON dbo.Orders.idCustomer = dbo.Customers.idCustomer WHERE ((dbo.Orders.OrderDate IS NULL) or (Payments.ResponseCode <> 1)) and (Customers.EMinvalid = 0 or Customers.EMinvalid is null) and (unsubscribe = 0 or Customers.unsubscribe is null) and Customers.DateRegistered > '2014-04-01' order by FirstName |
So what did we do? We joined our orders and payments tables so we could check orders and payments (duh). I only wanted…